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 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;
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.



Subscribe to this blog
Briefcase
Print
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