SQL Server: How to read from a stored procedure like a table

I wrote a really spiffy stored procedure for my client. It returns a bunch of rows, just like a table. Today the client asked if I could run the stored procedure with a WHERE clause (a perfectly reasonable request). I didn't know how to do that. But now I do:

It was actually quite simple. As far as I can see, the only trick here was I needed to have the user name and password of a SQL Server user:

-- Parameters that take single quotes
-- need two single quotes
Select * from Openrowset(
'SQLOLEDB.1'
,'MYSERVER';'joe_user';'password1'
,'exec mydatabase.dbo.my_stored_procedure ''7/1/2001'')
where address like '%elm%'
order by 7


This takes advantage of the OPENROWSET function. OPENROWSET lets you do magical things, like one-time ad hoc queries from Access and Excel and CSV. The way I'm using it is probably a bastardization, but so is everything that works well in SQL Server. As we said in the Marines, if it looks stupid but it works, it's not stupid.

Three things that can go wrong:
1) Don't forget to prefix the stored procedure name with the database name and owner (in my example, this is "mydatabase.dbo.".
2) The user you select has to have execute rights on the stored procedure.
3) this worked great in SQL 2000. It also worked great in SQL 2005, however, when I first went to do it, it failed with the following (horrible) error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server...

I had to go directly to the server, and run the "SQL Server Surface Area Configuration" utility (Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration). From there I added myself as an administrator, then clicked on "Surface Area Configuration for Features". Under "Database Engine" I clicked "Ad Hoc Remote Queries", then a checkbox appeared for "Enable OPENROWSET and OPENDATASET support". I checked this. After that, the error stopped happening and I was able to run my SQL statement.