TSQL to get users and permissions

Question

TSQL to get users and permissions

Pyman

SSChampion

Points: 13830
More actions
April 4, 2013 at 10:26 am

#272716

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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!

SQLCurious SSCrazy Points: 2339 More actions · Answer 2

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

Lowell SSC Guru Points: 323504 More actions · Answer 3

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!

Pyman SSChampion Points: 13830 More actions · Answer 4

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

Lowell SSC Guru Points: 323504 More actions · Answer 5

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!

Pyman SSChampion Points: 13830 More actions · Answer 6

Yes. Thanks.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams