Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

List of Database Users with Database Roles

By Santveer Singh,

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_Date	datetime 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 @mSql1	varchar(8000)

 select name 
	from	master.dbo.sysdatabases 
	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 as USERName, 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.

Total article views: 22285 | Views in the last 30 days: 30
Related Articles

Alter Database DBNAME Set Recovery Full. Gives error.

Alter Database DBNAME Set Recovery Full. Gives error.


query to get dbname,totalsize,reservedsize of all databases in sql 2000

query to get dbname,totalsize,reservedsize of all databases in sql 2000








Cursor - passing @dbname to INSERT STATEMENT

Concatentaion of @dbname from cursor + table name to use in FROM clause


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones