THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010

Intro

Of late I have been getting down and dirty with the Database Development tools in Visual Studio 2010. You may know this feature set by one of the plethora of other names it has had over recent years such as:

  • Visual Studio Team System for Database Professionals
  • DBPro
  • Datadude

For the rest of this post I’ll stick with the colloquial name that most people seem to recognise – datadude.

Regardless of which moniker you prefer I’m hoping you recognise the feature set that I am referring to here which includes:

  • Schema Compare
  • Data Compare
  • Offline database development
  • T-SQL refactoring
  • Database unit testing/Data generation
  • MSBuild integration
  • various other things…

I plan to write a detailed blog post in the coming weeks talking about my overall experiences with datadude but in the meantime I’m writing this blog post to focus on how I have chosen to manage security; by which I mean:

  • logins
  • users
  • role membership
  • user and role permissions

Security in Post-Deployment scripts

Datadude has built-in support for managing all of this stuff as we can see from this screenshot:

users roles logins in datadude

Notice that we have created a user, a login for that user and a role into which we later want to add the user. They exist as explicit objects within our project which means that datadude “knows” about them and that has a number of advantages including:

  • Datadude can include these objects in a schema compare
  • Datadude can do dependency tracking thus enabling offline development (e.g. If the login that is referenced in the CREATE USER statement does not exist then a design-time error will be raised)

That should be enough to justify using datadude to manage users and roles however I have come across a limitation that prohibits us from doing so; we do not deploy the same security model to each of our environments. The reasons for this are very familiar to you I’m sure, we have people that require different permissions on different environments (e.g. our developers will want to be able to issue DDL and UPDATE/INSERT/DELETE on our development environment but we definitely don’t want them to have the same on production).

As far as I can discern datadude does not have a good solution for dealing with this situation. I have not found a way of conditionally deploying objects depending on which environment we are deploying to. Actually that is not altogether true, we could have a Visual Studio configuration for each environment and include/exclude objects as we choose but that doesn’t play well with the build and deploy model of msbuild which requires that you build each one of those configurations; each of our builds takes about 10 minutes which, when multiplied by the number of environments, results in a total build time that takes too long for our continuous integration (CI) usage.

Hence we needed a better way to deploy our security model – we chose to use PostDeployment scripts. PostDeployment scripts do exactly what they say on the tin – they are scripts that get run after datadude has deployed the objects that it knows about. They get called (using SQLCMD syntax) from the Script.PostDeployment.sql file that exists in every datadude project:

post deployment script solution explorer

My chosen approach is to have a script called SecurityAdditionsWrapper.sql that gets called from Script.PostDeployment.sql. SecurityAdditionsWrapper.sql checks a SQLCMD variable to determine which environment is being deployed to and then calls the appropriate SecurityAdditionsXXX.sql script that is responsible for deploying the security model for that environment (where XXX is the environment).

I then have 3 subfolders that contain all the individual CREATE USER, role membership and permissions scripts that get called from SecurityAdditionsXXX.sql as and when required.

The screenshot below shows the contents of Script.PostDeployment.sql & SecurityAdditionsWrapper.sql and also the files as they exist in Solution Explorer:

!cid_image003_png@01CB274F

Some things to notice:

  1. I have a SQLCMD variable called $(DeployType) that will contain the value “DEV”, “UAT” or “PRD”
  2. All of my security scripts exist in Post-Deployment  -> SecurityAdditions
  3. In the commented-out code you can see that I was trying to be clever and dynamically build the name of the file to be executed using the $(DeployType) variable – the thinking being that if we later added a new environment type I wouldn’t need to change this script. Unfortunately even though this is valid SQLCMD syntax it does not work in datadude; I have reported that issue on Connect as a bug (with repro) at [Datadude] Valid SQLCMD syntax throws error at build time.

You may be wondering how we get a value into that $(DeployType) variable when using msbuild. Well, its pretty simple when you know how. First you need to include the variable in the sqlcmdvars file for your datadude project:

!cid_image004_png@01CB2755

You then need to pass in the appropriate value to that variable at deploy-time. We are using VSDBCMD.exe to run our deployments and the syntax to pass in a value for a variable is /p:PropertyName=PropertyValue. We are calling VSDBCMD.exe from inside an msbuild script which therefore looks like this:

<Exec Command="..\Tools\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:'Data Source=$(DatabaseServer);Integrated Security=True;Pooling=False' /DeployToDatabase:+ /ManifestFile:.\MyDB.deploymanifest /p:DeployType=&quot;$(DeployType)&quot; " />

$(DeployType) is of course also a property within our msbuild file, which I am drawing attention to in the bold section above.

Wrap-Up

In conclusion, I am not saying that this is the correct way to deploy a SQL Server security model using datadude, it just happens to be the method that I have chosen. I would really like to how people have solved this using datadude so if you have any anecdotes to share please put them in the comments.


One more thing…

Cranking all of those files out by hand is a laborious undertaking, especially if you have lots of users with lots of permissions. In our case we introduced datadude to be used with an existing system hence we already had all our security setup on existing servers but we had no good way of getting it into the structure that I explained above. Powershell and SQLCMD to the rescue!

I have produced a series of scripts that interrogate a named instance and produce all of the files that I explain above. Namely:

  • A file for each user which gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\Users\
  • A file for each principle that issues all the GRANT/DENY EXECUTE/SELECT/INSERT/UPDATE/DELETE for that principle, it gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\PermissionsSets\
  • A file that specifies all of the role memberships, for all database principles.

Creating the CREATE USER file and the file containing the permissions for each principle is quite involved. For that we need some nested loops that loop over (a) all the databases for which you want to scripts users and (b) all the principles in each database. It then dumps the appropriate files into the appropriate folders and then you have the simple task of adding them to your project and calling them from your Post Deployment script.

The files are stored in a zip folder on my OneDrive at http://1drv.ms/11tdPML

To run the Powershell script simply unzip all the files into the root folder of your solution and issue:

>GeneratePermissions.ps1 -SQLInstance instanceName -Environment XXX

changing SQLInstance & XXX accordingly (the $Environment variable is simply a suffix that is added to the names of some of the generated files). The Powershell script contains more detailed notes within it in the comments section at the top.

powershell script comments

Hope this helps!

Disclaimer: I’m giving this away for free and I won’t be supporting it so use at your own peril (in other words, don’t come here complaining that it doesn’t work on your environment). On the other hand if you find a problem and are able to fix it – please let me know Smile

UPDATE: I did actually find some problems with the scripts and have now uploaded a new set. I'm confident that they'll work this time.

UPDATE 2: I've updated the scripts again. They now create the SecurityAdditionsXXX.sql file as well as the individual CREATE USER & permissions files; SecurityAdditionsXXX.sql contains all of the calls to the relevant scripts for environment XXX.

SecurityAdditionsXXX.sql also issues all of the role membership statements as well. So just to clarify - you now no longer have a need to run the script that generates the role memberships - GeneratePermissions.ps1 takes care of everything.

UPDATE 3: Peter Schott has taken this, adapted it for hiw own needs, and sent it back to me so that I can share it here. His modified version of the scripts is at http://1drv.ms/1uimCKV and includes these changes (mainly to deal with roles):

1. Changed the Powershell script:
  * Added a section to handle role permissions. (really don't care for the way VS does it)
  * Tweaked the Database Principle section - for some reason, the variable in the PS script I extracted wasn't set properly so I just changed the names around
  * Added a bunch of lines of code to add to the Array. My Powershell's not too strong so wasn't sure about the best way to add new DBs/Projects to the DB Array. I ended up doing a bunch of sets. I know it's not ideal, but I just wanted to get it done one time.
  * Tweaked the section that creates the Users. If one of those logins doesn't exist, the grant permissions script would stop. Now I check to make sure that the login exists before the script runs. I sometimes get empty (Login = [])  sections. No ideas on that yet. I fix or delete that file as appropriate.

2. Added a "GetDatabaseRoleList.sql" file.  Gets the roles for the DB so we can run it through the Permissions file

3. Permissions SQL file. I tweaked this with a query that UNIONs the object permissions with the Schema permissions. I didn't break it down further
like you did, but our permissions are so messed up right now that it doesn't matter much to me. (we have far too many permissions granted to
individual users instead of roles because the prior DBA didn't understand the point of DB Roles for permissions and even then far too many permissions because we had a job that nightly granted all permissions on all objects to certain usernames :-P  )

And one more change that needs to be made which Peter informed me of later:

Ended up changing the GeneratePermissions.ps1 file to change all of the Out-File
to "Out-File -width 500 ".

After doing that, the permissions weren't truncated at 80 characters.  Also tweaked the Invoke-SQLCmd functions to use -MaxCharLength 500 so the output line would be long enough. That may have been overkill or there may be a better way to handle, but some of my permission lines were definitely longer than the allowed line lengths.

@Jamiet

P.S. I have used Powershell in the past but this is the first time I have really got into the inner workings of it and I’ve got to tell you – its just fantastic. The productivity you get is just incredible – if you don’t believe me have a look at the script herein and see how easy it is to generate a dataset from SQL Server and then loop over it inside your script. Its two lines of code - really fantastic stuff – I recommend any SQL Server pro go out and spend some time getting to know Powershell.
Published Wednesday, July 21, 2010 10:59 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter Schott said:

Awesome. I'm going to need to give this a try. Security has definitely been a problem for us and trying to manipulate the internal security structure is painful at best.

July 21, 2010 5:15 PM
 

jamiet said:

Paul,

nice one. Let me know how it goes for you.

July 21, 2010 5:35 PM
 

Christophe G said:

I seem to remember I once ran into an issue when doing :r within a BEGIN/END block: wouldn't work if the included sql script contained GO statements

July 23, 2010 4:23 AM
 

Peter Schott said:

Jamie - quick note on the users. I ended up adding an extra layer of security to those by also checking to see if the login existed in master.dbo.syslogins. If not, it bypassed creating the user. Adding an ":on error ignore" to the SecurityAdditionsWrapper file and letting it run may throw an error on role memberships or similar grants, but shouldn't break the script at that point. Otherwise, if you have one missing login, the rest of the permissions fail (or did for me).

I also have a script that generates the permissions not only for objects, but also for schemas. It's a little large to post here, though.

February 16, 2011 6:05 PM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM
 

Peter Schott said:

Wanted to add a little bit to this, I just modified my "Generate sp_addrolemember statements.sql" file to check for existence before granting permissions. I ran into a handful of scenarios where a user wasn't there or had been removed. That threw errors in SSDT, which at this time doesn't support the "ON ERROR IGNORE" option for sqlcmd.

New select statement is:

SET NOCOUNT ON

SELECT 'IF EXISTS (select * from sys.database_principals where name = ''' + mp.name + ''')

 EXEC sp_addrolemember N''' + rp.name + ''', N''' + mp.name + ''';' AS [Stmt]

FROM sys.database_role_members rm

INNER JOIN sys.database_principals rp

ON rm.role_principal_id = rp.principal_id

INNER JOIN sys.database_principals mp

ON rm.member_principal_id = mp.principal_id

WHERE mp.name NOT IN ('dbo')

ORDER BY rp.name

The other scripts I was using already addressed existence of logins prior to creation. This just adds another check before attempting to grant role membership.

January 11, 2013 5:46 PM
 

wattyjnr said:

Hi Jamie, great post!

I am looking to do the same thing now in my current project.

Question however, it looks like that your example is using one datadude project. I have a solution that contains 3 database projects. Where would you suggest that I put users/logins/roles as on our database server, all three databases use all of these objects? Currently I have the post deployment script in one of the database projects but it feels odd as the deployment would apply to the other 2 projects.

Does that make sense? Any ideas?

Thanks.

February 4, 2013 9:48 AM
 

wattyjnr said:

Also Jamie, a follow on from my previous question...where are your login file located in the post deployment scripts? I only see folders for RoleMemberships, Users and Permissions sets.

February 4, 2013 9:59 AM
 

jamiet said:

Hi wattyjnr, thanks for the comments.

Question. Do your three database projects deploy to 3 seperate databases? I will assume so for the purposes of this answer.

If you have server-scoped objects that pertain to all three databases then you might want to consider breaking them out into a fourth project that exists solely for the purpose of deploying server level objects. Users (because they are database-level objects) and database roles can live in the appropriate project and you can use database references to resolve logins that are named in the "server" project (although if you're using post-deployment scripts you won't need database references).

"where are your login file located in the post deployment scripts?"

Its a long time since I wrote this blog post but i suspect they were in a dedicated project for server-level objects (exactly as I propose immediately above)

Hope that helps

JT

February 4, 2013 5:32 PM
 

wattyjnr said:

Hi Jamie!

Sorry I should have mentioned in my earlier post that I am using SSDT, where it is probable that you build this solution using one of the previous incarnations of this tool. So in this instance, I have 3 separate database and currently have my server level objects within one of these databases being run as part of a post deployment script file, which seems a bit odd as they pertain to them all.

In light of this, do you still think I should create a separate SSDT project for solely handling these logins or just keep them in one of the projects.

As always, thanks for your great work and sharing of your knowledge in this area.

February 12, 2013 4:01 AM
 

jamiet said:

Hi Leon,

Based on what you've said here it might make sense to create them in a dedicated project. I'm a fan of encapsulation and encapsulating server level objects into a dedicated dacpac makes a lot of sense in my head.

Your mileage may of course vary.

JT

February 12, 2013 4:13 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement