List of Database Users with Database Roles

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 sp_GetListOfDBroles using the below script in the same database where you have created the table DBROLES.
   

Create procedure sp_GetListOfDBroles

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 sp_GetListOfDBroles
   
2. 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.