Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL to get users and permissions Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 10:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:34 AM
Points: 1,322, Visits: 1,730
I would like to use the below code in a cursor and loop through each and every database in an instance. Now I want to select the name of the database along the with the details the script selects. Is this possible?

select 
[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



“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Post #1438900
Posted Thursday, April 4, 2013 11:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438918
Posted Thursday, April 4, 2013 11:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
Lowell (4/4/2013)
....see how i modified it to sue sp_msForEachDb,....


Didn't realize we could sue other members' stored procedures Just an attempt to pump some humor, Lowell. No offense meant.

- Rex
Post #1438922
Posted Thursday, April 4, 2013 11:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
RexHelios (4/4/2013)
Lowell (4/4/2013)
....see how i modified it to sue sp_msForEachDb,....


Didn't realize we could sue other members' stored procedures Just an attempt to pump some humor, Lowell. No offense meant.

- Rex


ha! my fingers are fatter than i thought! Thanks Rex!


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438925
Posted Thursday, April 4, 2013 11:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:34 AM
Points: 1,322, Visits: 1,730
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
Post #1438941
Posted Thursday, April 4, 2013 11:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
glad i could help a little Sapan;
note that sp_msForEachDb is really is a cursor behind the scenes;
while cursors are generally frowned upon, when fiddling with metadata, this is one of those acceptable scenarios i think.
the other thing to note is that all the inner joins will automatically exclude orphaned users or users explicitly created WITHOUT LOGIN;
i know i create those kinds of users for testing or permissions issues in various databases.

create user [ClarkKent] without login;

Execute As USER= 'ClarkKent'
select user_name() --I'm Clark Kent!
select * from sys.objects --nothing there! if he can see anything, it's because someone granted to PUBLIC!

REVERT; --change back to superman
drop user [ClarkKent]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438948
Posted Thursday, April 4, 2013 12:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:34 AM
Points: 1,322, Visits: 1,730
Yes. Thanks.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Post #1438960
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse