• 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 at

    the 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