SQLServerCentral Article

List of Database Users with Database Roles


Sometime we come across specific request from our managers or users that needs to explore system tables and database. Today I got the same kind of request from my manager. He needs a list of all database users with database roles. I looked here and there but I didn’t find any help in BOL to fulfill this request. Then I decided to create a procedure to generate this information in Pivot table format. This procedure capture the user name with list of all fixed database roles. I further modified this process to store this information in table with current date time stamp, So that in future it will help audit the changes in fixed database roles. Here is the detail.

  1. Create the table DBROLES using below script in any database

( DBName sysname not null, 
  UserName sysname not null, 
  db_owner varchar(3) not null,
  db_accessadmin varchar(3) not null,
  db_securityadmin varchar(3) not null,
  db_ddladmin varchar(3) not null,
  db_datareader varchar(3) not null,
  db_datawriter varchar(3) not null,
  db_denydatareader varchar(3) not null,
  db_denydatawriter varchar(3) not null,
  Cur_Datedatetime not null default getdate() 

Please include all the user defined database roles in above table as Column Name.

  1. Create the Stored Procedure GET_LIST_OF_DBROLES using the below script in the same database where you have created the table DBROLES.

Create procedure Get_List_of_dbroles
declare @dbname varchar(200)
declare @mSql1varchar(8000)
 select name 
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
  Set @mSQL1 = 'Insert into DBROLES ( DBName, UserName, db_owner, db_accessadmin, 
                  db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
               db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+'
    Max(CASE RoleName WHEN ''db_owner''   THEN ''Yes'' ELSE ''No'' END) AS db_owner,
 Max(CASE RoleName WHEN ''db_accessadmin ''   THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
 Max(CASE RoleName WHEN ''db_securityadmin''  THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
 Max(CASE RoleName WHEN ''db_ddladmin''   THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
 Max(CASE RoleName WHEN ''db_datareader''   THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
 Max(CASE RoleName WHEN ''db_datawriter''   THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
    Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
 Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
       select b.name as USERName, c.name as RoleName 
      from ' + @dbName+'.dbo.sysmembers a '+char(13)+ 
'join '+ @dbName+'.dbo.sysusers  b '+char(13)+
       'on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
         on a.groupuid = c.uid )s 
   Group by USERName 
         order by UserName'
  --Print @mSql1
  Execute (@mSql1)
  FETCH NEXT FROM DBName_Cursor INTO @dbname
CLOSE DBName_Cursor

Please include the additional column in the above scripts also.

  1. Please Execute the Stored Procedure GET_LIST_OF_DBROLES

  1. Now you can get the list of all user with roles as below
Select * from DBROLES 
DBName        UserName         db_owner     db_accessadmin db_securityadmin...
------------- ---------------- ------------ -------------- ----------------...
UserDB1       User1            No           No             No              ...
UserDB1       User_RO          No           No             No              ...
UserDB1       User_RW          Yes          No             No              ...
UserDB1       Server1\USER_DB  No           No             No              ...
UserDB1       dbo              Yes          No             No              ...
UserDB2       User1            No           No             No              ...
UserDB2       User_RO          No           No             No              ...
UserDB2       User_RW          Yes          No             No              ...
UserDB2       Server1\USER_DB  No           No             No              ...
UserDB2       dbo              Yes          No             No              ...
UserDB3       User1            No           No             No              ...
UserDB3       User_RO          No           No             No              ...
UserDB3       User_RW          Yes          No             No              ...
UserDB3       Server1\USER_DB  No           No             No              ...
UserDB3       dbo              Yes          No             No              ...

PS: I have included the few column in result due to row size limitation.

  1. To get the list of rights for a specific user or database, please use the where clause as
Select * from DBROLES  where DBName = 'Userdb1'
Select * from DBROLES  where UserName = 'User1'

  1. You can schedule the above SP through Job to execute every week or month as per the requirement. Doing this we’ll able to find out when we assigned any rights to a user.

As always, your suggestion and comments are most welcome.


4.89 (19)




4.89 (19)