Solved

user list database role

Posted on 2011-03-07
2
400 Views
Last Modified: 2012-05-11
hi
  how can i list all users who has db_datareader and db_datawriter in a SQL 2005 production database ?
any scripts i can pull those info? thanks
0
Comment
Question by:tristonyip
2 Comments
 
LVL 2

Accepted Solution

by:
grossac earned 500 total points
ID: 35061313
http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/678c5758-7272-48da-9c7d-50b4b49c9615/

Code Snippet
CREATE procedure [dbo].[List_DBRoles]

(

@database nvarchar(128)=null,

@user varchar(20)=null,

@dbo char(1)=null,

@access char(1)=null,

@security char(1)=null,

@ddl char(1)=null,

@datareader char(1)=null,

@datawriter char(1)=null,

@denyread char(1)=null,

@denywrite char(1)=null

)

as

declare @dbname varchar(200)

declare @mSql1 varchar(8000)

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() 

)

 

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

Select * from #DBRoles 

where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND

((@user is null) OR (UserName LIKE '%'+@user+'%')) AND

((@dbo is null) OR (db_owner = 'Yes')) AND

((@access is null) OR (db_accessadmin = 'Yes')) AND

((@security is null) OR (db_securityadmin = 'Yes')) AND

((@ddl is null) OR (db_ddladmin = 'Yes')) AND

((@datareader is null) OR (db_datareader = 'Yes')) AND

((@datawriter is null) OR (db_datawriter = 'Yes')) AND

((@denyread is null) OR (db_denydatareader = 'Yes')) AND

((@denywrite is null) OR (db_denydatawriter = 'Yes'))

Open in new window

0
 

Author Comment

by:tristonyip
ID: 35061858
thank you so much, that's what i want. thanks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question