TSQL - do query on result set from a stored procedure

Question

Say if using sp_helplognis, want to view result set with filter UserName=db_owner. Is there any way besides output the original result set to a temp table and then query on that temp table? Thanks.

Answer 1

You can divert the resultset of a sproc into a new table, e.g.

create table #results (...)
insert into #results execute myproc
select * from #results where ...

It's a bit fiddly because you have to reverse-engineer the resultset data types pretty exactly to avoid casting errors.

Answer 2

Don't use sp_helplogins: use sys.server_principals

SELECT * FROM  sys.server_principals WHERE name = 'bob'

If you want the 2nd resultset of sp_helplogins, then you'd have to use c# or something because loading a temp table will only trap the 1st resultset.

The equivalent of sys.server_principals is sys.database_principals too

FYI: db_owner is in every database already. What are you really trying to do...?

Edit, after comment, something like:

EXEC sp_MSForEachDb '
USE ?
IF DATABASE_PRINCIPAL_ID (''myloginname'') IS NOT NULL
  EXEC sp_addrolemember ''db_datareader'', ''myloginname''
'