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.

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.

Advertisements

9 thoughts on “Quickly script permissions in SQL 2005+

  1. 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?

    Like

  2. 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)

    Like

  3. 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?

    Like

  4. 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?

    Like

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

    Like

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s