Quickly script permissions in SQL 2005+

I wanted an easy way to generate a script to recreate all DB object and schema permissions for a database. Searching online, I found several different examples, but none quite did everything I needed. I put this together from some examples and added it to my toolkit. This script will generate the appropriate T-SQL to grant permissions to DB Objects and Schemas.


SELECT
state_desc + ' ' + permission_name + ' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']' COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
ON sdp.grantee_principal_id = sdpr.principal_id
UNION
SELECT
state_desc + ' ' + permission_name + ' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']' COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
ON sdp.major_id = ss.SCHEMA_ID
AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

I know that a lot of you may have something that does this already, but figured it can’t hurt to have another snippet available to generate permission statements. If you make any enhancements, let me know. I’d love to keep this up to date so it’s helpful to others.

Comments
  1. Anonymous

    Awesome, just what I wanted. Works on SQL 2008 R2 as well.

    Thanks for sharing!

  2. Macclesfield Software Developer

    Fantastic. I have been trying to get SQL Server Management Studio to do this for me, using it's Generate Scripts option, but without success. This script is just the job!

  3. Anonymous

    I have been searching google all day for something like this…Brilliant! Thank You.

  4. sunny p

    Hi…

    am newbie, i need a tsql script for SQL SERVER 2005

    Requirements:

    I have 2 user accounts for the both accounts i need to find the all the permissions.

    Like…

    • The 2 users are mapped to which databases on the server?

    • What are the databases names?

    • What kind of permissions having on the mapped databases?

  5. Peter Schott

    I don't have a script for this directly. You can run the above on each database you have without too much trouble. If you want something more comprehensive, you may want to look at:

    http://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/

    Note – this generates HTML. Save the file as security.html or something similar and you can have a pretty good report of all permissions on your server. You can also pipe that output to a varchar(max) variable of some sort and use it to send DB Mail if you need this regularly. You'll have to do a little trial and error there, though as this isn't my script.


    If that's a bit much, you may want to try VikingDBA's script here: http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx

    (scroll down a little and look for the really long post by VikingDBA – it's worth registering if you don't have a SQLServerCentral.com account)

  6. Anonymous

    This script looks like exactly what I need BUT I need to run it on SQL2000 to recreate onto SQL2008 R2. Don't suppose you have a SQL2000 equivalent lying around anywhere?

  7. Peter Schott

    I don't know of anything for SQL 2000 off the top of my head, though I'm sure I had a script for it back in the day. I know you can exclude the entire “Schemas” section because that's new to SQL 2000. That would just leave the sysobjects and sysusers tables. The “schema” portion would be replaced by the object owner. I do not remember which table held the permissions, but I know it was usable because I had several tools and scripts to get that information back in the day. Perhaps the thread referenced above from VikingDBA could be helpful?

  8. Anonymous

    Very nice script, but it only handles adding permissions. What if the target db has permissions that need to be removed? It would be great if the script first revokes all permissions, so you have a clean db, and then adds them again. Tat waye you are sure all permissions are exactly the same as the source db.

    best regards, emphyrio

  9. Peter Schott

    Most of the time I'm using a SQL/DB Project to publish my database changes. Those have options to drop permissions/users not in the project. I use this script to generate some post-deploy privileges and grant them after the database has been created. I agree that something to handle permissions appropriately would be valuable, but if I were on 2005 or higher, I might consider writing some DDL triggers that would always fire and track creation of new users or granting of additional rights.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *