Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

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.

 

Comments

Posted by Dugi on 31 August 2009

Nice and very useful script, thank you Brian!

Posted by Mvking on 28 June 2011

U just saved my ass... !

U r right there are 101 scripts out there ... But ur script did what i exactly wanted. u got a 5 out of 5.

Posted by john.m.couch on 10 February 2012

Wonderful Script. Awesome. Thanks Mr. Kelley.

Posted by Minnesota - Viking on 3 April 2012

This script makes sense but i was wondering WHOSE permissions is it scripting ?

How do I specify particular login name whose permissions, i want to be scripted out.

Thanks

Leave a Comment

Please register or log in to leave a comment.