Quick view of User database and server roles

Question

Post reply

Quick view of User database and server roles

whenriksen

SSCarpal Tunnel

Points: 4752
More actions
July 18, 2013 at 5:19 am

#395160

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)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

aleksey donskoy Mr or Mrs. 500 Points: 565 More actions · Answer 1

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 Mr or Mrs. 500 Points: 565 More actions · Answer 2

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

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 3

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_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

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.