Store Your Code Now
Tools: String Manipulations Find Dupes My IP Json Formatter Lorem Ipsum More...
Discussion Options

SQL Server: List all Users and Roles

10/19/2016 3:35:13 PM by: CodeLocker

List all of the users in a SQL Server and their associated roles.

View Copy: Content Copied
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
 
WHERE     logintype = 'SQL_USER'

GROUP BY
 
dbname,username ,logintype ,create_date ,modify_date
 
ORDER BY create_date, DBName,username
Copy: Content Copied
×

Get Coding

Store your first code or add more code to your collection.
You must login to report abuse. ×