Get logins, databases users/roles and object level permission (T-SQL)

Introduction

This script contains three code parts, one to get the result of server level logins and related roles, one to get the result of user databases’ users and related roles, one to get the result of object level permission of specific database.

Scenarios

This script can be used to get the following results:

Script

You can use the script in the following way:

1. Open the Microsoft SQL Server Management Studio (SSMS) and connect to a server.

2. Drag the script to the SSMS.

3. Replace “Database Name” in the “USE Database Name” with the database you specify.

4. Run the script.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL
Edit|Remove
SELECTUs.nameASusernameObj.nameASobjectdp.permission_nameASpermissionFROMsys.database_permissionsdpJOINsys.sysusersUsONdp.grantee_principal_id = Us.uidJOINsys.sysobjectsObjONdp.major_id = Obj.id

Examples

Step 1: Open the Microsoft SQL Server Management Studio (SSMS) and connect to the server you want to.

 

Step 2: Drag the script to the SSMS.

Step 3:  Change the “USE Database Name” to “USE AdventureWorks2008”.

Step 4: Run the script.

Click the Execute button and get the following result:

Note: In the first table of the result, there are eight columns: SysAdmin, SecurityAdmin, ServerAdmin, SetupAdmin, ProcessAdmin, DiskAdmin, DBCreator, BulkAdmin.

The value of these columns is 1 or 0.
1: YES
0: NO 

Additional Resources

Forum Threads:

http://social.technet.microsoft.com/Forums/en-US/sqlsecurity/thread/b89f7ceb-d4ea-4dfd-9472-dfbc513b210b