Tuesday, May 14, 2013

SSDT: Setting Different Permissions per Environment

One of the areas that SSDT doesn't address adequately is permissions. Many users have different users, logins, and permissions set up across their environments, but SSDT takes a "one size fits all" approach and assumes that you will have the exact same configuration in each environment. While that should be the case for your schema, this area requires some interesting and somewhat involved techniques to handle well in SSDT.
Attribution - I'm borrowing heavily from Jamie Thomson's ( blog | twitter ) post
A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010
Jamie addressed this problem for the older DB Projects (Datadude, VSTSDB, DBPro, etc.). He came up with an excellent, if somewhat complex, solution to effectively handle pushing different permissions to different environments. This is my attempt to update this and distill it into a "how to" for SSDT and SQL Projects, but I freely admit that this is based on Jamie's excellent work.
In general, the steps we'll want to take are:
  1. Generate a set of SQL Logins that will be used on the server.
    1. These should be run on the servers or local box already - we will not use these within the SQL Project itself though you can if you wish.
  2. Generate the files for users/roles/permissions for each database project and environment.
  3. Add the files to your SSDT SQL Project
  4. Create and set a variable for your environments in your SSDT SQL Project.
  5. Adjust your Security Wrapper file to handle your environments.
  6. Adjust your post-deploy script to call the Security Wrapper.
First, let's look at what happens when you import Permissions by default into an SSDT SQL Project.
clip_image001
As you can see from the screenshot, we have a "Permissions" file containing granted permissions, a "RoleMemberships" file that adds users to various roles, a "Sales" file for the Sales schema, and 3 files each for the users - one for its schema, one for its login, and another for its user. Confusing, but not necessarily a problem until you need a release where User1 does not and will not exist on the server or where User2 belongs to a different domain than your production servers and can't and should not be created there because there's no trust relationship.
Let’s remove all of those files for objects other than Schemas from the project before we start. You’ll probably have to look at each file to verify before removing it from the project if you’re not sure which file is which. If you accidentally remove the files for the schemas, you’ll get errors and need to re-add a “Schema” to this security folder for each schema that is used.
 
Generate SQL Logins
This Microsoft KB article details the sp_help_revlogin stored procedure. Create this on your source database, run it, and use the output to re-create those logins elsewhere. This will copy all logins and users, but no permissions or database users.
 
Generate the Security Files through PowerShell
First, download the PowerShell_CreatePermissions.Zip file. Extract that to a folder on your hard drive.
Note - the script assumes that you will use Windows authentication to connect to your SQL Server. The script will need to be adjusted if you need to use SQL Authentication.
Edit the "GeneratePermissions.ps1" file. Replace the "DB1", "DB2", etc with whatever database names you'll be using. Also uncomment any of the extra lines that you'll need in the file. (There is likely a better way to do this in Powershell and I'm more than open to some suggestions on how to build up this array in a more concise manner.)
The assumption is made that the databasename == the projectname. If that's not the case, adjust your DB/Project names accordingly.
Open a PowerShell prompt in that folder after editing/saving the file.
Run the file using something like this commandline:
.\GeneratePermissions.ps1 -SQLInstance "localhost" -Environment DEV
"localhost" should be replaced with your servername
DEV in this case indicates that this will be tied to a variable value of "DEV" when the project is published.
I'd recommend running this for every environment at one time if you can. It will make adding the files to the project much easier.
If you run for several environments, you'll see a folder structure something like the following for each project (in this case for Adventureworks2012):
clip_image002
Inside your "SecurityAdditions" folder, you'll see the following files and folders:
clip_image003
Each of these files will be used to add permissions for each environment. In this case, we have permissions set for our Development, QA, Local, Production, and Staging environments.
Now, let's add the "SecurityAdditionsWrapper.sql" file from the files we extracted earlier into your "SecurityAdditions" folder. Feel free to edit it as needed to match the environment(s) you want to use for your database project. By default, it's set to look for the above files plus a "Default" file to be a catch-all. Adjust that so all files/environments match the files you created and save it.
 
Add the files to your SSDT SQL Project
Copy your newly created folder(s) to the appropriate project. Open each project, and you will notice that the files still don't show up. Click the project file. You should see a menu item that's now enabled to "show all files".
clip_image004
You'll see a bunch of files that aren't part of the project under a "Scripts" folder:
clip_image005
We want to add them to the project. Right-click the "Scripts" folder and select the option to "Include in Project". Click the Project file again and turn off the option to show all files.
  • We need to set all file properties to "Not in Build" or there will be errors when trying to build the project.
Unfortunately, we can't select just the folder to exclude from the build. We need to select the files. You can ctrl+click to select multiple files. Select all of the files, open the Properties tab, and change the option from "Build" to "None"
clip_image006
Create and set your Variable to change environments
Open the Project Properties window
clip_image007
We want to change the SQLCMD variable to add a new variable called "DeployType". Set it's default value to "Local" for now, or whatever environment for which you have a set of security scripts.
clip_image008
 
Adjust your Security Wrapper files
The SecurityAdditionsWrapper.sql file in the code set I've created comes with settings for "Dev", "QA", "Staging", "Production", "Local", and "Default". It's likely that you will not use all of these in your setting. One drawback to using SQLCMD to wrap all these files together is that if you don't have a file or set of files, the script will start throwing errors because an expected file doesn't exist. Edit the SecurityAdditionsWrapper.sql file to remove any of the configurations you will not use or change the names to match the environment values you used earlier.
 
Adjust your Post-Deploy script
Finally, we need to open your Post-Deploy script.
Add the following to your script:
:r .\SecurityAdditions\SecurityAdditionsWrapper.SQL
This will include your SecurityAdditionsWrapper file as part of your post-deploy process. See my earlier post on selectively running pre/post deploy scripts if you want finer control.



















































15 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. are you sure in the latest release with 2012 has still not resolved this issue?

    ReplyDelete
    Replies
    1. To my knowledge, this still has not been changed. If you have one set of accounts that are used for your Development environment and another for your Production environment, SSDT does not provide a way to push a different set of permissions.

      For example, my "Dev" login may be "Dev\MyApp". My "Production" login may be "Production\MyApp". Note that I have two different domains because the Development and Production domains do not have a trust relationship. When I try to push the login/user "Production\MyApp" to my development server, it will fail. Likewise, when I try to push "Dev\MyApp" to production, it will fail.

      If you have come across a way to tell SSDT to use a different set of users/logins per environment, I'd love to hear it. I'd love to see this as a feature in the future, but to my knowledge we need to use workarounds to get this working right now.

      Delete
    2. When I've been asked to set-up DBPro projects in VS2010 for 2005 SQL Server targets, I had to split a particular project into two kind of projects - 1) 'Core', 2) 'Dev' and 'Prod'. The 'Dev' and 'Prod' had the all environment specific definition. The 'Core' had everything like tables, sprocs, permissions, role definitions, etc. The 'Dev' one contained assignment of role memberships and logins for development environment only PLUS reference to 'Core' db project. The 'Prod' one was made in analogy to 'Dev'. You could quickly end up with three or six projects in solution, but it actually helped me to make the goal. If it was time to deploy, we deployed just the one which was relevant according to the source code branch.
      If there are such issues with SSDT, I'm wondering whether the similar setup would be the workaround.

      Delete
    3. If your setup worked well for VS DB Projects, it will likely work the same with SSDT. There are a lot of similarities in the way things are handled. I use the method above just because it makes more sense to me to handle in scripts and variables rather than partial projects. I haven't hit a definite need for custom code in partial projects yet, though I know there are a handful of projects in the works around me that might benefit from them.

      Delete
  3. Peter, great work on your blog, please keep posting on SSDT!
    I'm actually trying to set up a SSDT based LifeCycle, but I'm also having some trouble with data encryption and deployment.
    I should set different keys on UAT, QA, Production and Development environments. This sounds a lot like the work you've done on the last post but I'm newbie on this tech so I'll have a lot hard work on deducing how to do what I need.
    If you have plans on a new entry, please consider writing one on dacpac deploy with different field encryption on each env (for the same fields).
    Keep Up the Good Work!

    ReplyDelete
    Replies
    1. As noted in my StackExchange reply, this is not an area in which I've experimented as we don't use SQL encryption. I would guess that you can do this by setting up composite projects as Jamie Thomson outlines here: http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/10/deployment-of-client-specific-database-code-using-ssdt.aspx. I have not tried this, but I imagine creating a sub-project for each environment with their own encryption for the objects that require it could work.

      Delete
  4. hi. fixed few bugs in your powershell script.
    http://justpaste.it/j5y2

    ReplyDelete
    Replies
    1. Looks like you added a section to handle the functionality for users without logins. I didn't see any other changes, but appreciate the update. If you changed anything else that I missed, please feel free to comment on that and give yourself some credit for it. :)

      Delete
  5. one more fix. http://justpaste.it/j5zb
    if you have any questions feel free to contact me - dkultasev{ATAT}mail.ru
    Dmitrij

    ReplyDelete
  6. Thank You It was very helpful in handling the SSDT Security Issue.However I run into "CONNECT" error , while executing the post deploy scripts on the database.

    ReplyDelete
    Replies
    1. Not completely sure what error you're getting unless it's that you're trying to revoke or grant a CONNECT permission. I _thought_ that was in the scripts, but it may be something that needs to be added. Can you give more details on where the error is occurring and what it says?

      Delete
  7. Powershell_GeneratePermissions.zip won't download anymore. I ended up on a github repo https://github.com/Alex-Yates/GeneratePermissions

    I've managed to get the scripts to compile but I noticed that there is no script to concatenate all the different users/roles etc. and have to be done manually.

    ReplyDelete
    Replies
    1. Thanks for the note. I've updated the post w/ a link to the new file - PowerShell_CreatePermissions.zip. Alex Yates did take some of that and run with it, but I haven't kept up with that work too much. I saw you were over there, though.

      Delete
  8. I'm getting the SQL80001: Incorrect syntax near ':' due to the lines: :r .\RolePermissions\XXXRole__DEV.sql in the SecurityAdditionsDEV.sql. Please help!

    ReplyDelete