Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quick view of User database and server roles


Quick view of User database and server roles

Author
Message
whenriksen
whenriksen
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 532
Comments posted to this topic are about the item Quick view of User database and server roles

Wes
(A solid design is always preferable to a creative workaround)
aleksey donskoy
aleksey donskoy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 519
Interesting script.

2 things first:
1. If you have databases with different collation from your master database the script errors out on the name join. Amended as following:

EXEC ( 'USE [' + @name + '];
SELECT DB = DB_NAME(), RDP.name, MDP.name, SL.sysadmin, SL.securityadmin,
SL.serveradmin, SL.processadmin, SL.setupadmin, SL.bulkadmin, SL.diskadmin, SL.dbcreator
FROM sys.database_role_members AS DRM
INNER JOIN sys.database_principals AS RDP
ON DRM.role_principal_id = RDP.principal_id
INNER JOIN sys.database_principals AS MDP
ON DRM.member_principal_id = MDP.principal_id
INNER JOIN master.dbo.syslogins AS SL
ON MDP.name = SL.name COLLATE DATABASE_DEFAULT'


2. Database names, user names ... are not good as VARCHAR(50). Sript trucates names.

Beginning amended from:

USE master
/*Filters */
DECLARE @DBName VARCHAR(50) = NULL; --exact match
DECLARE @DBPrincipal VARCHAR(100) = NULL; --wildcard search

/*Script*/
DECLARE @name VARCHAR(50)
DECLARE @T TABLE
( DB VARCHAR(50)
, RolePrincipal VARCHAR(50)
, DBPrincipal VARCHAR(50)...


amanded as:

USE master
/*Filters */
DECLARE @DBName SYSNAME = NULL; --exact match
DECLARE @DBPrincipal SYSNAME = NULL; --wildcard search

/*Script*/
DECLARE @name SYSNAME
DECLARE @T TABLE
( DB SYSNAME
, RolePrincipal SYSNAME
, DBPrincipal SYSNAME ...

Then it runs fine.

Will attempt to make an SSMS custom report.
Thanks

Alex Donskoy
Greenberg Trauriq PA
Miami, FL
aleksey donskoy
aleksey donskoy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 519
See the custom SSMS report attached.
Works in SSMS 2005, 2008, 2008R2.
Added interactive sorting on all report colums so that you could se it by database name, by principal name, then examine all system admins, all data readers , and so on...

The file is zipped as the forum is not allowing .rdl uploads

Enjoy, and thanks for the scipt again. Very helpful to curious people.
Let me know if report having any issues.

Alex Donskoy
Attachments
whenriksen
whenriksen
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 532
Great feedback.

I hadn't run into the collation issues.

I haven't run into any truncation issues, but I know that our database names and usernames are limited in length. Your sysname approach is a good change also.

Thanks again for the improvements.

Wes
(A solid design is always preferable to a creative workaround)
SQL-DBA-01
SQL-DBA-01
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 Visits: 3212
One more thing...

If there is any login account but if that does not have any permission associated to it, the report does not pull out the report. It would have been better if the script can pull out all the login accounts and if any specific account does not have any priovilege, the entire row should stand empty.

Thanks.
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