Template Driven Sql Generation

  • [アーティクル]

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.

Pre-Build Events

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" 

    9: )

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.

Advantages

  • Object references in the schema model will be validated during the build process
  • Deploy will accurately compare the identifiers for security objects between the project and the target database

Disadvantages

  • Refactoring the security object will not modify the definition in the configuration specific scripts.
  • Schema Compare will compare the identifier defined in the script which is “In Build” to the target. This may result in unexpected differences.
  • If you need to create a new Visual Studio configuration for each target SQL server you will be deploying to.

Post-Deployment File

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:

    1: /*

    2: 

    3: Post-Deployment Script Template

    4: 

    5: */

    6:  

    7: IF (('$(DatabaseName)') = 'TR9_Database_Dev')

    8: BEGIN

    9:     PRINT N'Creating Sandbox Security Objects...';

   10:     IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-DEV\TR9_CONTROL')

   11:     BEGIN

   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')

   15:         BEGIN

   16:             CREATE USER [TR9Control] FOR LOGIN [TR9-DEV\TR9_CONTROL];

   17:             EXECUTE sp_addrolemember @rolename = N'TR9_Control', @membername = N'TR9Control';

   18:         END

   19:     END 

   20:     IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-DEV\TR9_USERS')

   21:     BEGIN

   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')

   24:         BEGIN

   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';

   28:         END

   29:     END 

   30: END

   31: ELSE

   32: BEGIN

   33:     PRINT N'Creating Integration Security Objects...'

   34:     IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-INT\TR9_CONTROL')

   35:     BEGIN

   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')

   39:         BEGIN

   40:             CREATE USER [TR9Control] FOR LOGIN [TR9-INT\TR9_CONTROL];

   41:             EXECUTE sp_addrolemember @rolename = N'TR9_Control', @membername = N'TR9Control';

   42:         END

   43:     END 

   44:     IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TR9-INT\TR9_USERS')

   45:     BEGIN

   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')

   48:         BEGIN

   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';

   52:         END

   53:     END 

   54: END

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.

Advantages

  • All of your security objects are defined in one script.

Disadvantages

  • Since the security objects are not defined in the database schema they will not participate in Refactoring or Schema Compare.
  • You may need conditional in the script for each target SQL server you will be deploying to.  If your security objects are similar enough you may be able to use SqlCmd variables to instead of the IF/ELSE scheme I used.

Visual Studio Extensibility

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:

  • %ProgramFiles%MSBuild\Microsoft\VisualStudio\v9.0\TeamData\ProcessSqlTemplate.Task.Targets
  • %ProgramFiles%Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies\ProcessSqlTemplateAddIn.dll
  • %ProgramFiles%Microsoft Visual Studio 9.0\VSTSDB\ProcessSqlTemplate.Task.dll
  • %ProgramData%\Microsoft\MSEnvShared\Addins\ProcessSqlTemplateAddIn.AddIn

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

    2: --*GO

    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:

    1: <ItemGroup>

    2:     <SqlCommandVariableOverride Include="SVR=$(SVR)" Condition="'$(SVR)' != ''"/>

    3: </ItemGroup>

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

Advantages

  • The database developer only needs to implement one SQL script defining the security object.
  • The identifier for the security object is represented as a variable that is bound to the correct value when the project is built.
  • Deploy will correctly compare the project’s schema definition with the target database.
  • It makes use of SqlCmd variables which are overridable on the command line.

Disadvantages

  • The Add-In and MSBuild task need to be installed on each Developer’s computer as well as the TFS Build server.
  • The .dbproj file must be modified to include the MSBuild target.
  • Refactoring will not modify the schema template definitions.

Visual Studio Extensibility Source Code

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.

Conclusion

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.

Comments

  • Anonymous
    August 11, 2009
    So what does this template enable?  There are already vars that can be used (like $(DatabaseName)) to substitute values. What is the difference?

  • Anonymous
    August 11, 2009
    Ah, you can't use parameters in object names.  That is why this is needed.  It recreates the items to switch after each compile. What about .sqlpermissions?  Is that not used then?

  • Anonymous
    August 11, 2009
    The comment has been removed

  • Anonymous
    August 11, 2009
    The .sqlpermissions file is used as an input in the build and deploy mechanisms.  If you have permissions changing then you'll have to use method #1 or #2 - the add-in isn't for you. As for your addin problems I'll have to check my code.  What you're seeing is the BOM at the beginning of a unicode file.  I'm out of the office right now, but when I'm back I'll review the code and see if I can repro.  thanks!

  • Anonymous
    August 24, 2009
    I've updated the code and the msi to check for the BOM before writing the file.  So if you happen to be using ASCII I won't change it to unicode-8 on you.

  • Anonymous
    March 25, 2012
    The comment has been removed

  • Anonymous
    March 26, 2012
    Try running the project with diagnostic output.  Using the output you'll be able to see the value of all the MSBuild properties.  If ProjectDir is empty there should be another one you could use.  In VS go to Tools.Options.Projects and Solutions and set the MSBuild project build output verbosity to Diagnostic.   Now, when you build, you'll get a ton of text in the output pane.

  • Anonymous
    February 14, 2013
    can you check the links for the downloads none of them seem to work any more

  • Anonymous
    February 15, 2013
    SkyDrive must have been down.  The links are working again for me.  Try the folder link directly: skydrive.live.com