http://www.sqlservercentral.com/articles/Administering/listofdatabaseuserswithdatabaseroles/1545/
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 TBL_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.
Create the Stored Procedure GET_LIST_OF_DBROLES using the below script in the same database where you have created the table DBROLES.
---------------------------------------------------
USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[Get_List_of_dbroles] Script Date: 11/19/2008 10:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[usp_RechtenLijst]
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 TBL_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
---------------------------------
4. Please include the additional column in the above scripts also.
5. Please Execute the Stored Procedure GET_LIST_OF_DBROLES
Now you can get the list of all user with roles as below
PS: I have included the few column in result due to row size limitation.
6. 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.
Abonneren op:
Reacties posten (Atom)
Geen opmerkingen:
Een reactie posten