How do i get usernames for everydatabase including the admins

Question

Post reply

How do i get usernames for everydatabase including the admins

nagkarjun1

Ten Centuries

Points: 1020
More actions
February 7, 2014 at 12:46 pm

#283526

How do i get the usernames for each and every database including the admins
database Name Login Name
This is for the auditing purposes.
I tried several scripts. My username is listed as sys admin and have access to all databases but while running scripts i dont see my name under all databases.
please advise

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here is a script to get a full blown audit of security

http://jasonbrimhall.info/2010/03/19/security-audit/

It's beyond what you requested but will cover all of the bases.

Another script I saw recently is here that does a security script too

http://www.sqlservercentral.com/blogs/kendalvandyke/2014/02/04/using-sysobjects-when-scripting-permissions-youre-missing-something/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

for sql users or windows users, you need to UNION ALL something like this to explicitly show the sysadmins, since they inherit the permissions via their server role

SELECT r.name,'sysadmin',dbs.DatabaseName

FROM master.sys.server_principals r

JOIN master.sys.server_role_members m ON r.principal_id = m.member_principal_id

JOIN master.sys.server_principals p ON m.role_principal_id = p.principal_id

CROSS JOIN (SELECT name AS DatabaseName from sys.databases) dbs

WHERE p.name = 'sysadmin';

for non-explicit logins that get their permissions from windows groups, you have to do something similar, where you use xp_logininfo to enumerate all the users that belong to the groups, which you can then join to the user's permissions;

i didn't bother looking for the roles the groups belong to, but here's something i built for another post a day or two ago, that enumerates all windows users belonging to windows groups

IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL

DROP TABLE [dbo].[#TMP]

CREATE TABLE [dbo].[#TMP] (

[ACCOUNT NAME] NVARCHAR(256) NULL ,

[TYPE] VARCHAR(8) NULL ,

[PRIVILEGE] VARCHAR(8) NULL ,

[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,

[PERMISSION PATH] NVARCHAR(256) NULL )

DECLARE @cmd VARCHAR(MAX);

SELECT @cmd = s.Colzs

FROM(SELECT

Colzs = STUFF((SELECT ';' + 'INSERT INTO #TMP EXEC master..xp_logininfo @acctname = ''' + name +''',@option = ''members'' '

FROM master.sys.server_principals

WHERE type_desc = 'WINDOWS_GROUP'

AND name NOT LIKE '%$%' --avoid errors like Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQL$MSSQLSERVER1', error code 0x8ac.

FOR XML PATH('')

),1,1,'')

) s

SET @cmd = REPLACE(@cmd,';',';' + CHAR(13) + CHAR(10))

print @cmd

exec(@cmd)

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

A more basic approach might be something like this

sp_MSforeachdb 'use ?; SELECT DB_NAME() AS DBName,name AS UName, type_desc AS Usertype

FROM sys.database_principals

WHERE type like ''[GUS]''

AND sid IS NOT NULL;

'

edit fix CS

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nagkarjun1 Ten Centuries Points: 1020 More actions · Answer 4

Thank you guys for the reply's. Really Appriciate them

Lowell script worked like charm. modified little

SELECT r.name,dbs.DatabaseName

FROM master.sys.server_principals r

JOIN master.sys.server_role_members m ON r.principal_id = m.member_principal_id

JOIN master.sys.server_principals p ON m.role_principal_id = p.principal_id

CROSS JOIN (SELECT name AS DatabaseName from sys.databases) dbs

It gave the desired results which my management wanted.

Thanks once again guys

nagkarjun1 Ten Centuries Points: 1020 More actions · Answer 5

nagkarjun1

Ten Centuries

Points: 1020

February 7, 2014 at 3:19 pm

#1687261

How about the SQL server 2000 version?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

nagkarjun1 (2/7/2014)
Thank you guys for the reply's. Really Appriciate them
Lowell script worked like charm. modified little
SELECT r.name,dbs.DatabaseName
FROM master.sys.server_principals r
JOIN master.sys.server_role_members m ON r.principal_id = m.member_principal_id
JOIN master.sys.server_principals p ON m.role_principal_id = p.principal_id
CROSS JOIN (SELECT name AS DatabaseName from sys.databases) dbs
It gave the desired results which my management wanted.
Thanks once again guys

You understand that is only getting you the list of people that are in the sysadmins role?

There are plenty of other logins that you would be missing via that query. Getting everything is a two part thing as Lowell said.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events