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.
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_Datedatetime not null default getdate() ) GO
Please include all the user defined database roles in above table as Column Name.
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 @mSql1varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
frommaster.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.
- Please Execute the Stored Procedure GET_LIST_OF_DBROLES
- 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.
- 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'
- 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.