locked
DB Pro and permission standard

    Question

  •  Hi,
     
    I suppose I am after a standard to create users, roles, permissions and role membership.  I have a project, which has three environments (dev, UAT, prod).  Some users and roles exist on all environments (global) others only exist on one (local).  I have currently coded global ones in the security folder, and applied their permissions through the post deployment script.

    Local ones I've put in the post deployment script, and made parts into variables, (is this the right thing to do or can I put variables in the user folder?):

    IF NOT EXISTS (SELECT 1 FROM sys.sysusers WHERE [name] = '$(ASUser)')  
     
    CREATE USER [$(ASUser)] FOR LOGIN [$(ASUser)]  
     

    Problem with all of this is, if I don't include parts in the build process, it drops users/roles (as expected).  And then errors appear in the post deployment saying can not add user to a role because it doesn't exist.

    It seems a little messy, and the post deployment script doesn't check the integrity.  So you kind of have to double check, users, roles, permissions and memberships.  Seeing as I am doing all this manual checking and trying to figure out which file has which bit of code in,  is there not a better place to put all the code?

    Cheers
    Matt



    Wednesday, December 17, 2008 10:57 AM

Answers

  • What we used to do, prior to compound projects, was use post deployment scripts that checkd for the server name. Then, based on the server name we could set up individual security and permissions, etc.

    Now, we're working it out still but so far it looks good, we're using compound projects to create the security. The base database, the one that has everything common across all environments, and then a environment specific database that references the base database, but adds security different than the base database. It seems to work well so far.
    • Marked as answer by Matt Tolhurst Friday, December 19, 2008 8:42 AM
    Thursday, December 18, 2008 3:27 PM
  • Hi Matt,

    SQLCMD variables can not be used in sql indentifiers, they should only be used within statement bodies with the exception of the database name and database server.

    As Grant explains,  you can target specific enviroments using composite projects if you have a base with a common set of objects (users, roles, etc). If you have this common denominator between your environments then this approach may work well.

    The other option is to put your users, roles, and perms into the post deployment script and use SQLCMD variables to control the exectuion by enivironment suing SQLCMD variables.

    This is an area that we are investigating improvements for future releases.
    Thursday, December 18, 2008 6:45 PM
    Moderator

All replies

  • Yeah, I'd be keen to know the best practice for this as well...
    Thursday, December 18, 2008 1:01 AM
  • What we used to do, prior to compound projects, was use post deployment scripts that checkd for the server name. Then, based on the server name we could set up individual security and permissions, etc.

    Now, we're working it out still but so far it looks good, we're using compound projects to create the security. The base database, the one that has everything common across all environments, and then a environment specific database that references the base database, but adds security different than the base database. It seems to work well so far.
    • Marked as answer by Matt Tolhurst Friday, December 19, 2008 8:42 AM
    Thursday, December 18, 2008 3:27 PM
  • Hi Matt,

    SQLCMD variables can not be used in sql indentifiers, they should only be used within statement bodies with the exception of the database name and database server.

    As Grant explains,  you can target specific enviroments using composite projects if you have a base with a common set of objects (users, roles, etc). If you have this common denominator between your environments then this approach may work well.

    The other option is to put your users, roles, and perms into the post deployment script and use SQLCMD variables to control the exectuion by enivironment suing SQLCMD variables.

    This is an area that we are investigating improvements for future releases.
    Thursday, December 18, 2008 6:45 PM
    Moderator
  • Hi,

    Thanks for your advice on this area.  With regards to the composite projects, are there any references to how to achieve what Grant has suggested?  I seem to only be able to find what it means and not how to set it up.

    Thanks again
    Matt
    Friday, December 19, 2008 8:41 AM
  • Hi Matt,

    The link below is to a walkthrough that shows you how to set up composite projects. In your scenario, you will be splitting your schema based on the security objects.

    http://msdn.microsoft.com/en-us/library/dd193415.aspx

    You might have 5 projects assuming all databases are on the same server. If they are not, you may have a server project for each server if the configuration is unique to each environment.

    1.    A Server project containing your server-level objects (Logins, etc)
    2.    A ("Base") Database project containing your user database-level objects(Tables, Views, Sprocs, and any other common objects applicable to all your environments)
    3.    A Database project containing your security objects for your DEV environment (users, roles, perms, etc.)
    4.    A Database project containing your security objects for your TEST environment (users, roles, perms, etc.)
    5.    A Database project containing your security objects for your PROD environment (users, roles, perms, etc.)

    Your DEV database composite project is made up of the following references:
    Project 3 references 2 (without database and server variables)
    Project 3 references 1 (this should use the database variable = 'master')
    This targets your DEV environment.

    Your TEST database composite project is made up of the following references:
    Project 4 references 2 (without database and server variables)
    Project 4 references 1 (this should use the database variable = 'master')
    This targets your TEST environment.

    Your PROD database composite project is made up of the following references:
    Project 5 references 2 (without database and server variables)
    Project 5 references 1 (this should use the database variable = 'master')
    This targets your PROD environment.

    Friday, December 19, 2008 7:50 PM
    Moderator
  • Thanks Barclay

    Monday, December 22, 2008 8:46 AM