Contents

1 Script


1 Script #

SELECT 
   CASE dp.state_desc 
     WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' 
     ELSE dp.state_desc  
   END  
     + ' ' + dp.permission_name + ' ON ' + 
   CASE dp.class 
     WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']' 
     WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']' 
     WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']' 
   END  
     + ' TO [' + USER_NAME(grantee_principal_id) + ']' + 
   CASE dp.state_desc 
     WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' 
     ELSE ';'  
   END  
   COLLATE DATABASE_DEFAULT 
FROM sys.database_permissions dp 
  LEFT JOIN sys.all_objects o 
    ON dp.major_id = o.OBJECT_ID 
WHERE dp.class < 4 
  AND major_id >= 0 
  AND grantee_principal_id <> 1;  
http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/08/28/quick-2005-2008-script-to-export-permissions.aspx