T-SQL: Retrieve all users and associated roles for ALL databases
A frequent inquiry concerning databases’ security is to retrieve the database role(s) associated with each user for auditing or troubleshooting purposes.
Each database user (principal) can be retrieved from sys.database_principals and the associated database roles can be retrieved from sys.database_role_members
The following code runs against ALL the databases using SP_MSForeachdb and all roles for one principal is concatenated in one row
DECLARE @DB_USers TABLE (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime) INSERT @DB_USers EXEC sp_MSforeachdb ‘ use [?] SELECT ”?” AS DB_Name, case prin.name when ”dbo” then prin.name + ” (”+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?”) + ”)” else prin.name end AS UserName, prin.type_desc AS LoginType, isnull(USER_NAME(mem.role_principal_id),””) AS AssociatedRole ,create_date,modify_date FROM sys.database_principals prin LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and prin.is_fixed_role <> 1 AND prin.name NOT LIKE ”##%”’ SELECT dbname,username ,logintype ,create_date ,modify_date , STUFF( ( SELECT ‘,’ + CONVERT(VARCHAR(500),associatedrole) FROM @DB_USers user2 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName FOR XML PATH(”) ) ,1,1,”) AS Permissions_user FROM @DB_USers user1 GROUP BY dbname,username ,logintype ,create_date ,modify_date ORDER BY DBName,username
The code applies few filters:
– No Guest user account; you should NOT enable a guest account for a production database.
– Skip fixed database roles such as DB_OWNER , db_datawriter,db_datareader,….etc
– Principals with NULL SID ; those are internal to the DB such as INFORMATION_SCHEMA & sys
Remarks
– SYSADMINS don’t need to be part of explicit database’s users list to gain access as they have access to everything on server. You can get list of SYSADMIN users by running following code
SELECT p.name AS [Name] ,r.type_desc,r.is_disabled,r.create_date , r.modify_date,r.default_database_name FROM sys.server_principals r INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id WHERE r.type = ‘R’ and r.name = N’sysadmin’
– If a windows login is part of a windows group then you may need to look at the members of this Windows group to identify who can access this database.
– A user may still be revoked access to database indvidual objects even the role allows access normally.
Following blog has a query to list permissions on indvidual objects
https://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions–_2800_2_2900_.aspx
HTH
M
Share this
You May Also Like
These Related Stories
Comments (1)