Recently Duke Kamstra and I demonstrated how you can use Visual Studio 2008 Database and Server projects to deploy Sql Server permission objects to your various Sql Server instances. It’s common to develop on a local box with admin rights, and then restrict those rights as you start deploying to test, pre-production and, eventually, your production Sql Server instance. Database projects assume your schema model is static – an assumption that may work for Tables, Views and Sprocs, but not so much for Logins. In this blog I’ll show you three ways to manage SQL security principles changing as a schema is deployed to different staging environments: Pre-Build Events, Post-Deployment scripts and Visual Studio Extensibility.
With this mechanism you’re swapping in new source files prior to each build. In the sample solution below I have two Visual Studio Configurations – ‘Sandbox’ and ‘Integration’. For each security object I’ve created 2 new files and navigated to the Properties Window to change their Build Action to ‘Not in Build’.
The pre-build event action is found in the project properties ‘Build Events’ tab:
In the pre-build textbox I’ve inserted a script for swapping in new security objects just prior to the build.
1: if [$(Configuration)] == [Integration] (
2: xcopy /r /y "$(ProjectDir)User1.user.integration.sql" "$(ProjectDir)User1.user.sql"
3: echo copied "$(ProjectDir)User1.user.integration.sql" to "$(ProjectDir)User1.user.sql"
4: ) else if [$(Configuration)] == [Sandbox] (
5: xcopy /r /y "$(ProjectDir)User1.user.sandbox.sql" "$(ProjectDir)User1.user.sql"
6: echo copied "$(ProjectDir)User1.user.integration.sql" to "$(ProjectDir)User1.user.sql"
7: ) else (
8: del "$(ProjectDir) User1.user.sql"
Note that I’m using “xcopy /r” which overwrites the target file even if it’s marked readonly. I consider this a reasonable approach because, even if you do have this file in version control, it contains no useful information. The interesting, versionable information actually resides in the .sandbox.sql and .integrationl.sql files.
Another approach to creating security objects is to place all your security-based T-Sql in your Post-Deployment Script.
Here’s an example of a Post-Deployment script used in this way:
3: Post-Deployment Script Template
7: IF (('$(DatabaseName)') = 'TR9_Database_Dev')
9: PRINT N'Creating Sandbox Security Objects...';
10: IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-DEV\TR9_CONTROL')
12: CREATE LOGIN [TR9-DEV\TR9_CONTROL] FROM WINDOWS;
13: EXECUTE sp_addsrvrolemember @loginame = N'TR9-DEV\TR9_CONTROL', @rolename = N'dbcreator';
14: IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TR9-DEV\TR9_CONTROL')
16: CREATE USER [TR9Control] FOR LOGIN [TR9-DEV\TR9_CONTROL];
17: EXECUTE sp_addrolemember @rolename = N'TR9_Control', @membername = N'TR9Control';
20: IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-DEV\TR9_USERS')
22: CREATE LOGIN [TR9-DEV\TR9_USERS] FROM WINDOWS
23: IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TR9-DEV\TR9_USERS')
25: CREATE USER [TR9Users] FOR LOGIN [TR9-DEV\TR9_USERS];
26: EXECUTE sp_addrolemember @rolename = N'TR9_Users', @membername = N'TR9Control';
27: EXECUTE sp_addrolemember @rolename = N'TR9_Users', @membername = N'TR9Users';
33: PRINT N'Creating Integration Security Objects...'
34: IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-INT\TR9_CONTROL')
36: CREATE LOGIN [TR9-INT\TR9_CONTROL] FROM WINDOWS;
37: EXECUTE sp_addsrvrolemember @loginame = N'TR9-INT\TR9_CONTROL', @rolename = N'dbcreator';
38: IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TR9-INT\TR9_CONTROL')
40: CREATE USER [TR9Control] FOR LOGIN [TR9-INT\TR9_CONTROL];
41: EXECUTE sp_addrolemember @rolename = N'TR9_Control', @membername = N'TR9Control';
44: IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-INT\TR9_USERS')
46: CREATE LOGIN [TR9-INT\TR9_USERS] FROM WINDOWS
47: IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TR9-INT\TR9_USERS')
49: CREATE USER [TR9Users] FOR LOGIN [TR9-INT\TR9_USERS];
50: EXECUTE sp_addrolemember @rolename = N'TR9_Users', @membername = N'TR9Control';
51: EXECUTE sp_addrolemember @rolename = N'TR9_Users', @membername = N'TR9Users';
Once again, since the objects do not exist as part of the project system model they cannot participate in the schema differencing process which occurs prior to deployment. Any errors in the objects or your script will not appear until you deploy.
Now I’ll introduce you to the Visual Studio Add-In ProcessSqlTemplateAddIn and associated MSBuild Task. These components use Visual Studio Extensibility to pre-process your T-Sql scripts and bind the value of SQLCMD variables to the respective values defined in the active project configuration. When you select a project configuration the MSBuild task scans the project for template script files. Any variables that are discovered in the templates are replaced with the respective value.
Install the Add-In and MSBuild Task using this msi. The MSI will install the following components:
After installing, create a Server Project and in that project create a file called ‘Login1.login.template.sql’. The “template.sql” at the end of the filename indicates to the Add-In that this is a template script. Copy the following text to that file:
1: --*CREATE LOGIN [$(SVR)\TR9_CONTROL] FROM WINDOWS
3: --*CREATE LOGIN [$(SVR)\TR9_USERS] FROM WINDOWS
The “--*” indicates that this text is part of the template.
Next edit your project properties to create two new .sqlcmdvars file – one for your Sandbox and one for your Integration configuration. In the Sandbox .sqlcmdvars file define $(SVR) as “SVR-DEV”. In your integration .sqlcmdvars file define $(SVR) as “SVR-INT”.
Now we need to chain our new MSBuild task into the build process. As part of the ProcessSqlTemplateAddIn MSI a targets file was installed. This target file contains the definition of the ProcessSqlTemplate MSBuild task. Unload the project and insert the following Import statement into your project file after the ‘Microsoft.Data.Schema.SqlTasks.targets’ import statement:
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\ProcessSqlTemplate.Task.Targets" />
Reload the project and you’ll see that switching configurations will alter the contents of your ‘Login1.login.template.sql’ script according to the value of your sqlcmdvars for that configuration. A sample project is included here.
Before I leave this top there’s one advantage I’d like to explain. Because this approach is bound to your configurations SqlCmd variables file you are able to provide these variable values on the MSBuild commandline. To do this create a new ItemGroup in your project file with the name of the SqlCmd variable you may want to override:
2: <SqlCommandVariableOverride Include="SVR=$(SVR)" Condition="'$(SVR)' != ''"/>
With this ItemGroup in place I can now execute MSBuild supplying the configuration as well as an override for the $(SVR) sqlcmd variable. There’s a good forum post on this topic here.
msbuild /t:rebuild /property:Configuration=Sandbox;SVR=Patrick TR9_Database\TR9_Database.sln
The source code for the Add-in, MSBuild task and the installer is located here. To compile this you’ll need Visual Studio 2008, the Visual Studio 2008 VSSDK, and Votive 3.0 (I used 3.0.4707.0).
The MSBuild addin is located in the ProcessSqlTemplateAddIn project. Visit the source file Connect.impl.cs for the most interesting details of the implemention. Note that Connect implements IVsUpdateSolutionEvents and IVsSolutionEvents. These events help the addin listen to database projects opening/closing as well as when their configuration changes. When a configuration does change ProcessAllTemplates is called which calls on the MSBuild task to do the template processing.
The MSBuild task is located in the ProcessSqlTemplate.Task project. Take a look at ProcessSqlTemplateTask.Execute for its entry method.
In this blog we’ve discussed three possibilities for deploying server objects to different Sql Server instances from a single Visual Studio Solution. To install the msi or view the source code you’ll need Visual Studio Team System 2008 Database Edition GDR.
The source code for the Visual Studio ProcessSqlTemplate MSBuild Task and Add-in is here.
The sample project demonstrating how to use the Add-In is here.
The ProcessSqlTemplateAddIn MSI is located here.