Blog Post

Quick 2005/2008 Script to Export Permissions

,

I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:

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 THEN 'DATABASE::[' DB_NAME() + 
']'
     
WHEN THEN 'OBJECT::[' SCHEMA_NAME(o.schema_id) + '].[' o.[name] 
']'
     
WHEN 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;
  

It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.

I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating