Solved

user list database role

Posted on 2011-03-07
2
393 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now