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

CREATE TABLE DBROLES 
( 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() 
)
GO

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
as
declare @dbname varchar(200)
declare @mSql1	varchar(8000)

DECLARE DBName_Cursor CURSOR FOR 
 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

WHILE @@FETCH_STATUS = 0
 BEGIN
  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
 END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go

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: 21929 | Views in the last 30 days: 38
 
Related Articles
FORUM

Alter Database DBNAME Set Recovery Full. Gives error.

Alter Database DBNAME Set Recovery Full. Gives error.

FORUM

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

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

FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

FORUM

Cursor - passing @dbname to INSERT STATEMENT

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

Tags
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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