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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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.
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.
Awesome, just what I wanted. Works on SQL 2008 R2 as well.
Thanks for sharing!
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!
I have been searching google all day for something like this…Brilliant! Thank You.
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?
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)
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?
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?
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
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.