Tuesday, February 4, 2014

Using sys.objects When Scripting Permissions? You're Missing Something!

I suspect most DBAs have a script in their T-SQL toolbelt which scripts out database permissions for a specific user (or all users). I do, too,  and for years I've used it and always assumed it did the job - until recently when I found that permissions on a certificate weren't in the output for a user I was looking at.

My script, like just about every other one you can find with your search engine of choice, joined sys.database_permissions against sys.objects to get specific permissions on objects (e.g. tables, views, and stored procedures). While sys.objects does contain quite a few things, there are quite a few securables that it does not - specifically:

  • Assemblies
  • Asymmetric Keys
  • Certificates
  • Database Principals (users, application roles, and database roles)
  • Fulltext Catalogs
  • Fulltext Stoplists
  • Schemas
  • Service Broker Message Types
  • Service Broker Remote Service Bindings
  • Service Broker Routes
  • Service Broker Services
  • Service Broker Service Contracts
  • Symmetric Keys
  • Types
  • XML Schema Collections

Fortunately, there are catalog views for everything listed above and all we have to do is join sys.database_permissions against each of them to ensure that we're including all securables.

SMO also has the same issue - the Database.EnumObjectPermissions method does not include permissions on any of the aforementioned securables. That means the only way to retrieve all database permissions with SMO is to iterate through each of the securables listed above and call the object's EnumObjectPermissions method in addition to calling the Database.EnumObjectPermissions method - not pretty or efficient. (For what it's worth, version 1.0.2.1 of SQL Power Doc does NOT do this, meaning some permissions could be excluded from the documentation. I'll have a new beta that fixes this posted within the next week or two)

Below is the updated version of my script, which you can also download from SkyDrive OneDrive.



/*********************************************************************************************
Security - Script Database Level Permissions (2005+)

Description:
   Scripts database permissions (including objects) for a specific user or all database users

   (C) 2014, Kendal Van Dyke (mailto:kendal.vandyke@gmail.com)

Version History:
   v1.00 (2014-02-03)

License:
   This query is free to download and use for personal, educational, and internal
   corporate purposes, provided that this header is preserved. Redistribution or sale
   of this query, in whole or in part, is prohibited without the author's express
   written consent.

Note:
   Execute this query in the database that you wish to script permissions for
  
*********************************************************************************************/
SET NOCOUNT ON

DECLARE    
@user SYSNAME;

-- Set this to NULL for all users
-- SET @user = 'DOMAIN\USER';
SET @user = NULL;

SELECT 'USE ' + QUOTENAME(DB_NAME()) + ';' AS '--Database Context';


-- Logins
SELECT 'CREATE USER [' + usr.name + '] FOR LOGIN [' + susr.name + ']'
      
+ CASE WHEN usr.TYPE = 'S'
              
THEN ' WITH DEFAULT_SCHEMA=[' + usr.default_schema_name + '];'
              
ELSE ';'
        
END AS ' --Logins'
FROM   sys.database_permissions AS PERM
       INNER JOIN
sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
      
INNER JOIN sys.server_principals AS susr ON usr.sid = susr.sid
WHERE  PERM.permission_name = 'CONNECT'
      
AND susr.sid != 0x01
      
AND usr.NAME = COALESCE(@user, usr.NAME);


-- Role Members
SELECT 'EXEC sp_addrolemember @rolename = ' + QUOTENAME(usr.name, '''')
       +
', @membername = ' + QUOTENAME(usr2.name, '''') + ';' AS '--Role Memberships'
FROM   sys.database_principals AS usr
      
INNER JOIN sys.database_role_members AS rm ON usr.principal_id = rm.role_principal_id
      
INNER JOIN sys.database_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
WHERE  /*usr2.is_fixed_role = 0*/
      
usr2.sid != 0x01
      
AND usr2.NAME = COALESCE(@user, usr2.NAME)
ORDER BY rm.role_principal_id ASC;


-- Object permissions
WITH   cteObject ( [major_id], [name], [class_desc], [class_name] )
        
AS ( SELECT   obj.[object_id] AS [major_id] ,
                      
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.'
                      
+ QUOTENAME(obj.name) AS [major_name] ,
                      
'OBJECT_OR_COLUMN' AS [class_desc] ,
                      
'OBJECT' AS [class_name]
              
FROM        sys.all_objects AS obj
              
UNION ALL
              
SELECT  assembly_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ASSEMBLY' AS [class_desc] ,
                      
'ASSEMBLY' AS [class_name]
              
FROM        sys.assemblies
              
UNION ALL
              
SELECT  asymmetric_key_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ASYMMETRIC KEY' AS [class_desc] ,
                      
'ASYMMETRIC_KEY' AS [class_name]
              
FROM        sys.asymmetric_keys
              
UNION ALL
              
SELECT  certificate_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'CERTIFICATE' AS [class_desc] ,
                      
'CERTIFICATE' AS [class_name]
              
FROM        sys.certificates
              
UNION ALL
              
SELECT  principal_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'DATABASE_PRINCIPAL' AS [class_desc] ,
                      
CASE type_desc
                        
WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
                        
WHEN 'DATABASE_ROLE' THEN 'ROLE'
                        
ELSE 'USER'
                      
END AS [class_name]
              
FROM        sys.database_principals
              
UNION ALL
              
SELECT  fulltext_catalog_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'FULLTEXT_CATALOG' AS [class_desc] ,
                      
'FULLTEXT CATALOG' AS [class_name]
              
FROM        sys.fulltext_catalogs
              
UNION ALL
              
SELECT  stoplist_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'FULLTEXT_STOPLIST' AS [class_desc] ,
                      
'FULLTEXT STOPLIST' AS [class_name]
              
FROM        sys.fulltext_stoplists
              
UNION ALL
              
SELECT  message_type_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'MESSAGE_TYPE' AS [class_desc] ,
                      
'MESSAGE TYPE' AS [class_name]
              
FROM        sys.service_message_types
              
UNION ALL
              
SELECT  remote_service_binding_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'REMOTE_SERVICE_BINDING' AS [class_desc] ,
                      
'REMOTE SERVICE BINDING' AS [class_name]
              
FROM        sys.remote_service_bindings
              
UNION ALL
              
SELECT  route_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ROUTE' AS [class_desc] ,
                      
'ROUTE' AS [class_name]
              
FROM        sys.routes
              
UNION ALL
              
SELECT  [schema_id] AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SCHEMA' AS [class_desc] ,
                      
'SCHEMA' AS [class_name]
              
FROM        sys.schemas
              
UNION ALL
              
SELECT  service_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SERVICE' AS [class_desc] ,
                      
'SERVICE' AS [class_name]
              
FROM        sys.services
              
UNION ALL
              
SELECT  service_contract_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SERVICE_CONTRACT' AS [class_desc] ,
                      
'CONTRACT' AS [class_name]
              
FROM        sys.service_contracts
              
UNION ALL
              
SELECT  symmetric_key_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SYMMETRIC_KEY' AS [class_desc] ,
                      
'SYMMETRIC KEY' AS [class_name]
              
FROM        sys.symmetric_keys
              
UNION ALL
              
SELECT  user_type_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'TYPE' AS [class_desc] ,
                      
'TYPE' AS [class_name]
              
FROM        sys.types
              
UNION ALL
              
SELECT  xml_collection_id AS [major_id] ,
                      
QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
                      
+ QUOTENAME(name) COLLATE database_default ,
                      
'XML_SCHEMA_COLLECTION' AS [class_desc] ,
                      
'XML SCHEMA COLLECTION' AS [class_name]
              
FROM        sys.xml_schema_collections
            
)
  
SELECT  CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
                
ELSE 'GRANT'
          
END + SPACE(1) + PERM.permission_name + ' ON ' + obj.class_name
          
+ ' :: ' + obj.name + CASE WHEN cl.column_id IS NULL THEN SPACE(0)
                                      
ELSE ' (' + QUOTENAME(cl.name) + ')'
                                
END + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
          
+ CASE WHEN PERM.state <> 'W' THEN ';'
                  
ELSE ' WITH GRANT OPTION;'
            
END AS '--Object Level Permissions'
  
FROM    sys.database_permissions AS PERM
           INNER JOIN
cteObject AS obj ON PERM.major_id = obj.major_id
                                          
AND PERM.class_desc = obj.class_desc
          
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
          
LEFT JOIN sys.all_columns AS cl ON PERM.minor_id = cl.column_id
                                              
AND PERM.major_id = cl.[object_id]
                                              
AND PERM.class_desc = 'OBJECT_OR_COLUMN'
  
WHERE   usr.is_fixed_role = 0
          
AND usr.sid != 0x01
          
AND usr.NAME = COALESCE(@user, usr.NAME)
  
ORDER BY PERM.permission_name ASC ,
          
PERM.state_desc ASC;


-- Database permissions (non-object specific)
SELECT CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
            
ELSE 'GRANT'
      
END + SPACE(1) + PERM.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
      
+ CASE WHEN PERM.state <> 'W' THEN ';'
              
ELSE ' WITH GRANT OPTION;'
        
END AS ' --Database Level Permissions'
FROM   sys.database_permissions AS PERM
       INNER JOIN
sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE  PERM.major_id = 0
      
AND usr.NAME != 'dbo'
      
AND usr.NAME = COALESCE(@user, usr.NAME)
ORDER BY PERM.permission_name ASC ,
      
PERM.state_desc ASC;