SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List of Database Users with Database Roles


List of Database Users with Database Roles

Author
Message
santveer
santveer
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 2
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columni


1234
Anthony Butler
Anthony Butler
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 11

Elegant and simple in design, nice in execution. While the scripts themselves are useful, the written example of how to use a cursor was even more useful.

Now I can start re-writing that awful procedure I inherited from a now defunct developer to make it work like it should have done all along!

Thanks!

Ant Butler.


Gregg-59533
Gregg-59533
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 4
Thank you for this as I am working on the same issue right now.



SAinCA
SAinCA
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 702

Suggest adding:

SET @mSQL1 = 'TRUNCATE TABLE dbo.DBRoles'

EXECUTE (@mSql1)

After the initial DECLARE's to clear out the table if you run the Proc more than once. (Assumes you created the table under dbo). (Chose dynamic SQL in keeping with the style of the proc.)

Alternativey, you may want to include some "NOT EXISTS" logic to prevent adding duplicate role information, assuming the cur_date field enables you to track changes over time.


rschaeferhig
rschaeferhig
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 119

Here's a version that doesn't require the DBRoles table and executes the final SELECT with parameters so you can just execute List_DBRoles and specify things like "@dbo='Y' " or "@user='RS02130' " and get the results back immediately. It's pretty flexible.

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'))
GO





I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
DrChips
DrChips
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 3

...MS Supplies the following:

Exec sp_HelpLogins
Exec sp_HelpUser
Exec sp_HelProtect
Exec sp_HelpSrvRoleMember
Exec sp_HelpRoleMember





rschaeferhig
rschaeferhig
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 119

Based on some quick empirical testing the above SP's only apply to the current database. The new SP posted above lists all relevant data (OK, we could add SID if you really want it) for all databases on the server assuming you have appropriate access to dbo.sysmembers and dbo.sysusers. I believe in a standard SQL Server setup everyone has READ access to these tables so the SP consolidates a lot of information in one place. The MS SP's (it seems) have to be run in each DB separately and the information correlated.





I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
DrChips
DrChips
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 3

> The MS SP's (it seems) have to be run in each DB separately and the information correlated.

Use Master
Go
Exec master..sp_HelpUser
Exec pubs..sp_HelpUser
Exec northwind..sp_HelpUser





rschaeferhig
rschaeferhig
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 119
Still, you have to run it against each DB as opposed to the posted SP which gets all the DB names from master.dbo.sysdatabases and recurses the list. OK, it uses a cursor. When I have some time maybe I'll see if I can convert it to set-based processing.



I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
ivan gonzalez-195804
ivan gonzalez-195804
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1

Great piece of work, I have meant to do this for quite awhile...

Two minor suggestions:

One, may want to use consistent casing in case you run this procedure in a "case sensitive" environment.

Secondly, may want to add a check to bypass any database that is temporarily offline:

and databaseproperty(name, 'IsOffline') = 0

Cheers!


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search