Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Thursday, July 28, 2016 12:14 PM
Points: 1,407, Visits: 1,910
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 @ 6:01 PM
Points: 14,469, Visits: 38,076
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1438918
Posted Thursday, April 4, 2013 11:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 26, 2016 9:35 AM
Points: 105, Visits: 428
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 @ 6:01 PM
Points: 14,469, Visits: 38,076
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
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: Thursday, July 28, 2016 12:14 PM
Points: 1,407, Visits: 1,910
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 @ 6:01 PM
Points: 14,469, Visits: 38,076
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
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: Thursday, July 28, 2016 12:14 PM
Points: 1,407, Visits: 1,910
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