The Complete SQL Server 2005 Permissions Report

Richard Lu, 2010-05-10 (first published: 2009-07-16)

Script Description:

The script provides detailed access permission report on all levels, i.e. server, database, object and column, of SQL Server 2005 for all logins. Users would also be able to customize the report result by specifying two parameters: @loginName and @dbName at the beginning of the script to retrieve permission assignments on particular logins and databases. Reports of permissions possessed by System fixed roles, e.g. public and SQLAgentOperatorRole, can be excluded from the report by simply removing the comment marks on all the "type NOT IN (‘R’)" condition in the Where-clause in the script.

How to Use:
Copy and paste the script to SQL Server 2005 Database Engine Query Window and run the query. By default, the query will report back with permissions on all Logins and databases.

To get a customized report on particular logins and/or databases, you will need to specify the @loginName and @dbname parameters. Below are examples of these parameters:

Example 1: Returns the complete permission report for all logins (Default setting)
SET @loginName = ‘%’
SET @dbName = ‘%’

Example 2: Returns the permission report for Windows logins ‘NorthAmerica\JSmith1’ in ReportServer DB.
SET @loginName = ‘NorthAmerica\JSmith1’
SET @dbName = ‘ReportServer’

Example 3: Returns the permission report for SQL logins Like ‘reader%’ in all databases.
SET @loginName = ‘reader%’
SET @dbName = ‘%’

Result Sets:

The script provides three sets of results. One for SQL Server level, one for database level, and the last one for object\column level. Below is a screenshot of an example.

 

NOTE:
Orphan database users
Database users without a corresponding login in the database level permission matrix suggest that the users are orphan users. Exceptions go to some database roles, such as "guest", "public", and etc. It should be a best practise to address any orphan users with the database administrators.

    /**************************************
Name: The Complete SQL Server 2005 Permissions Report

Author: Richard Lu

Date Created: 05/04/2009

Description:
The script provides detailed access permission report on all levels, i.e. server, database, 
object and column, of SQL Server 2005 for all logins. Users would also be able to customize 
the report result by specifying two parameters: @loginName and @dbName at the beginning of 
the script to retrieve permission assignments on particular logins and databases. 
Reports of permissions possessed by System fixed roles, e.g. public and SQLAgentOperatorRole, 
can be excluded from the report by simply removing the comment marks on all the "type NOT IN ('R')" 
condition in the Where-clause in the script.

****************************************/
USE master
GO
SET NOCOUNT ON
DECLARE @loginName sysname, @dbName sysname

/* Set the two Parameters here. By defaul All logins and databases will be reported */
SET @loginName = '%' -- e.g. 'NorthAmerica\JSmith1'
SET @dbName = '%' -- e.g. 'ReportServer'


-- Get Server Role Level Info
SELECT 
 suser_sname(p.sid) AS Login_Name, 
 p.type_desc AS [Login_Type], 
 r.is_disabled,
 r.name AS Permission_Name, 
 r.type_desc AS Permission_Type, 
 p.create_date, p.modify_date
FROM
 master.sys.server_principals r
 LEFT OUTER JOIN master.sys.server_role_members m ON r.principal_id = m.role_principal_id
 LEFT OUTER JOIN master.sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE p.name LIKE @loginName 
 --AND p.type NOT IN ('R')
UNION
SELECT 
 suser_sname(prin.sid) AS Login_Name, 
 prin.type_desc AS [Login_Type], 
 prin.is_disabled,
 perm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission_Name, 
 perm.class_desc AS Permission_Type, 
 prin.create_date, prin.modify_date
FROM master.sys.server_permissions perm
 LEFT OUTER JOIN master.sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
 LEFT OUTER JOIN master.sys.server_principals grantor ON perm.grantor_principal_id = grantor.principal_id
WHERE prin.name LIKE @loginName 
 --AND prin.type NOT IN ('R')
ORDER BY Login_Name, r.name


-- Retrieve DB Role Level Info
DECLARE @DBRolePermissions TABLE(
 DatabaseName varchar(300), 
 Principal_Name sysname, 
 Login_Name sysname NULL, 
 DB_RoleMember varchar(300), 
 Permission_Type sysname)

INSERT INTO @DBRolePermissions
EXEC sp_MSforeachdb '
 SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, 
 roles.Name AS Role_Member_Name, roles.type_desc
 FROM [?].sys.database_role_members r 
 LEFT OUTER JOIN [?].sys.database_principals users on r.member_principal_id = users.principal_id
 LEFT OUTER JOIN [?].sys.database_principals roles on r.role_principal_id = roles.principal_id
 --WHERE users.type not in (''R'')'

-- Capture permissions generated FROM sys.database_permissions
INSERT INTO @DBRolePermissions
EXEC sp_msforeachdb '
 SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, 
 r.Permission_Name AS DB_RoleMember, r.class_desc
 FROM [?].sys.database_permissions r 
 LEFT OUTER JOIN [?].sys.database_principals users on r.Grantee_principal_id = users.principal_id
 WHERE r.class_desc = ''DATABASE'''

SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, DB_RoleMember AS Permission_Name, Permission_Type
FROM @DBRolePermissions 
WHERE (ISNULL(Login_Name, '') LIKE @loginName OR ISNULL(Principal_Name, '') LIKE @loginName)
 AND DatabaseName LIKE @dbName
ORDER BY Principal_Name, DatabaseName, DB_RoleMember


-- Get Object/Column Level Permissions
DECLARE @ObjectPermissions TABLE (
 DatabaseName varchar(300), 
 Principal_Name sysname, 
 Login_Name sysname NULL, 
 ObjectType sysname, 
 ObjectName varchar(500), 
 PermissionName varchar(300), 
 state_desc varchar(300), 
 Grantor varchar(300))

DECLARE @CurrentDB sysname, @SQLCmd varchar(4000)
DECLARE DBCursor CURSOR FOR 
 SELECT [name] 
 FROM master.sys.databases 
 WHERE state = 0 AND [name] LIKE @dbName

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @CurrentDB

WHILE (@@fetch_status = 0)
BEGIN

-- Capture permissions generated FROM sys.database_permissions
SET @SQLCmd = 'USE [' + @CurrentDB + '] 
 SELECT ''' + @CurrentDB + ''' AS DatabaseName,
 grantee.name AS Principal_Name, 
 suser_sname(grantee.sid) AS Login_Name, 
 p.class_desc AS ObjectType,
 CASE p.class_desc
 WHEN ''SCHEMA'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.schemas objects WHERE objects.schema_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''DATABASE'' THEN CONVERT(sysname, p.class_desc) COLLATE Latin1_General_CI_AS
 WHEN ''OBJECT_OR_COLUMN'' THEN 
 CONVERT(sysname, ISNULL((SELECT o.name + ''.'' + objects.name FROM sys.columns objects WHERE objects.[object_id] = p.major_id and objects.column_id = p.minor_id), o.name)) COLLATE Latin1_General_CI_AS

 WHEN ''DATABASE_PRINCIPAL'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.database_principals objects WHERE objects.principal_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''ASSEMBLY'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.assemblies objects WHERE objects.assembly_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''TYPE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.types objects WHERE objects.user_type_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''XML_SCHEMA_COLLECTION'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.xml_schema_collections objects WHERE objects.xml_collection_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''MESSAGE_TYPE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.service_message_types objects WHERE objects.message_type_id = p.major_id)) COLLATE Latin1_General_CI_AS

 WHEN ''SERVICE_CONTRACT'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.service_contracts objects WHERE objects.service_contract_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''REMOTE_SERVICE_BINDING'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.remote_service_bindings objects WHERE objects.remote_service_binding_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''ROUTE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.routes objects WHERE objects.route_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''FULLTEXT_CATALOG'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.fulltext_catalogs objects WHERE objects.fulltext_catalog_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''SYMMETRIC_KEY'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.symmetric_keys objects WHERE objects.symmetric_key_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''CERTIFICATE'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.certificates objects WHERE objects.certificate_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''ASYMMETRIC_KEY'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.asymmetric_keys objects WHERE objects.asymmetric_key_id = p.major_id)) COLLATE Latin1_General_CI_AS
 ELSE CONVERT(sysname, ''n\a'') COLLATE Latin1_General_CI_AS
 END AS ObjectName,
 Permission_name, state_desc, grantor.name AS Grantor
 FROM [' + @CurrentDB + '].sys.database_permissions p 
 LEFT OUTER JOIN [' + @CurrentDB + '].sys.database_principals grantee on p.grantee_principal_id = grantee.principal_id
 LEFT OUTER JOIN [' + @CurrentDB + '].sys.all_objects o on p.major_id = o.[object_id]
 LEFT OUTER JOIN [' + @CurrentDB + '].sys.database_principals grantor on p.grantor_principal_id = grantor.principal_id
 WHERE p.class_desc not in (''DATABASE'') 
 --AND grantee.type not in (''R'') '

INSERT INTO @ObjectPermissions
EXEC (@SQLCmd)

FETCH NEXT FROM DBCursor into @CurrentDB
END

CLOSE DBCursor
DEALLOCATE DBCursor


SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, ObjectName, ObjectType,
 PermissionName, state_desc, Grantor
FROM @ObjectPermissions 
WHERE ISNULL(Login_Name, '') like @loginName OR ISNULL(Principal_Name, '') like @loginName
ORDER BY DatabaseName, Principal_Name, ObjectName, PermissionName

Rate

4.29 (14)

Share

Share

Rate

4.29 (14)

Related content

Worst Practice – Bad Connection Strings and Bad Info in Sysprocesses

Andy returns to the Worst Practice series this week with a short article looking at how connection strings in applications affect what you see in sysprocesses. Perhaps less controversial (in our opinion) that some of the other worst practices, this is something easy to fix and definitely worth fixing! Read the article and post a comment – explore other points of view! Readers posting a comment will be entered in a drawing for a copy of the SQL Server 2000 Resource Kit.

Andy Warren

2003-08-01

15,102 reads

Logins, Users, and Roles – Getting Started

Do you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique.

Andy Warren

2005-09-30 (first published: 2003-02-19)

35,333 reads

The Case for SQL Logins – Part 1

Andy says Windows Authentication "is bad". What? That's not what Microsoft says! Heck, that's not even what we say! Everyone knows NT authentication is the way to go. Then again, when was the last time Andy wrote an article that wasn't worth reading?! Read the article, rate it and add a comment – and automatically be entered in a drawing for a copy of SQL Server 2000 Performance Tuning donated by Microsoft Press.

2 (1)

Andy Warren

2002-06-25

12,610 reads