Lowell (4/4/2013)
something like this seems to work for me, see how i modified it to sue sp_msForEachDb, and to INSERT INTO a #temp table, and read the results atthe end.
CREATE TABLE [dbo].[#TMP] (
[DATABASENAME] NVARCHAR(128) NULL,
[LOGIN TYPE] VARCHAR(3) NULL,
[SRVLOGIN] CHAR(45) NULL,
[SRVROLE] CHAR(45) NULL,
[DBUSER] CHAR(25) NULL,
[DBROLE] CHAR(25) NULL)
EXEC sp_msForEachDB
' INSERT INTO #TMP
select ''?'' As DbName,
[Login Type]=
case sp.type
when ''u'' then ''WIN''
when ''s'' then ''SQL''
when ''g'' then ''GRP''
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
[?].sys.database_principals as dbp on sp.sid=dbp.sid join
[?].sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
[?].sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id '
SELECT * FROM #TMP
Cool..thanks lowell..I dont even need a cursor now.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams