http://www.sqlservercentral.com/blogs/brian_kelley/2007/11/06/sql-server-showing-permissions-for-a-given-database-user/

Printed 2014/04/17 09:05AM

SQL Server: Showing permissions for a given database user

2007/11/06

Building upon my post from last Tuesday, if you know all the roles for a given user, you'll probably want all the permissions as well. In prior versions of SQL Server, the way to go was the system stored procedure sp_helprotect. However, sp_helprotect is stuck in legacy SQL Server 2000 permissions. In other words, the new securables in SQL Server 2005, such as SCHEMA, aren't reported by this stored procedure. They key is to use the dynamic management view (DMV), sys.database_permissions.

The SQL Doctor, Louis Davidson, posted on how to use this DMV to query table permissions about a year ago. Taking that and combining it with the CTE for listing all the roles for a user, we come up with:

DECLARE @username NVARCHAR(128);
SET @username '<Database User>';

WITH CTE_Roles (role_principal_id)
AS
    
(
    
SELECT role_principal_id
        
FROM sys.database_role_members
        
WHERE member_principal_id USER_ID(@username)
        
UNION ALL
    
SELECT drm.role_principal_id
        
FROM sys.database_role_members drm
                
INNER JOIN CTE_Roles CR
                
ON drm.member_principal_id CR.role_principal_id
    
)

SELECT DISTINCT
 
USER_NAME(CR.role_principal_idPrincipalName,
 
COALESCE(SO.type_descDPerms.class_descObjectType,
 
CASE DPerms.class
   
WHEN THEN
     
CASE DPerms.minor_id
       
WHEN THEN SCHEMA_NAME(SO.schema_id) + '.' SO.name
        
ELSE SCHEMA_NAME(SO.schema_id) + '.' SO.name ' ('
              
COL_NAME(DPerms.major_idDPerms.minor_id) + ')' END
    WHEN 
THEN SCHEMA_NAME(DPerms.major_idEND ObjectName,
 
DPerms.state_desc ' ' DPerms.permission_name Permission
FROM (
     
SELECT role_principal_id
     
FROM CTE_Roles
     
UNION ALL
     
SELECT USER_ID('public')
     
UNION ALL
     
SELECT USER_ID(@username)) CR
 
INNER JOIN sys.database_permissions DPerms
   
ON CR.role_principal_id DPerms.grantee_principal_id
 
LEFT JOIN sys.objects SO
   
ON DPerms.major_id SO.OBJECT_ID
WHERE DPerms.class IN (13)
  AND 
DPerms.major_id 0
ORDER BY PrincipalNameObjectNamePermission;




Technorati Tags: DATABASE | SQL | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2005 | Security | Database Security


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.