SSDT and Windows Database Login

Question

Hi,

I have only just converted VS2010 database projects to SSDT (still in VS2010 for now) and really like the changes regarding logins - before I was having to create 2x projects: one for database level objects and another for instance level (which featured just 1 login).

It's been some time since I investiagted this, but has SSDT made it easier (possible?) to specify Windows logins for the database?

Apologies if I've missed improvements but I tried to do this in Database Projects ~18 months ago and gave up.

Our current setup is that the database projects specify a database user, a role, the role membership. Our installer is still reponsible for creating a windows login and then associating that login with the database user (specified in SSDT).

For SQL logins I'm able to use SSDT and its a great experience to specify everything in-project rather than writing additional installer commands.

Answer 1

Just to recap, you want to include the *logins*, which are not application-scoped, in an SSDT project?  To do this, you can right-click somewhere on your project (by convention in the 'Security' folder) and click 'Add -> New item'.  In the list, select 'Login (Windows Auth)', and this will provide you with a windows login template.  Just replace the identifier with the login name (e.g. [DOMAIN\MyUserName]) and that should be all you need!

One thing to bear in mind: if you're updating your database using Schema Compare, you'll have to go to the 'Options' window, and select 'Logins' on the 'Object Types' tab, otherwise they won't be updated.

I hope this answers your question!

Tom

Answer 2

Hi Tom, thanks for your fast response.

As always I left out some information. Our application isn't deployed inside of our own IT environment or a hosted application, its distributed. So I've never know the DOMAIN part of the identifier until deployment time.

Last time I tried this, I think I ran into the rule that a SQLCMD variable cannot be part of an identifier - is that correct? Is there anyway I can create a login as per: '[DOMAINVARIABLE]\LoginName'.

Answer 3

Unfortunately there isn't an *easy* way of doing this (see this thread edit: and this one) ... but it is still possible.  Here are the steps that should see you right:

1. Open your database project properties page and click on the 'SQLCMD Variables' tab.  Add a variable, e.g. put "$(Domain)" in the Variable column.  If there is a sensible default value, put that in; otherwise leave that column blank.  Similarly if there's a testing value you want to put in for your local testing.
2. Create a new script file in your project (right-click -> Add -> Script...) and call it PostDeploy, or similar.  Click on the script, and go to its properties page (Alt+Enter).  Click on the 'Build Action' and change it to "Post Deploy".
3. Edit the file, and paste in
   CREATE LOGIN [$(Domain)\username] FROM WINDOWS;
   (where username is the login name you need, of course).  If you need several of these, put them all in, remembering to put "GO" between each one.

Your project should now build correctly, and if you've set a default or local value for the $(Domain) variable, you can just deploy it.

You should note that, unlike a normal 'CREATE' statement in SSDT, by putting it in the post-deploy section, this will try to re-create the LOGIN every time you run the deploy.  For this reason, you might want to surround each CREATE LOGIN with a check to see if it already exists.  Also, you can only have *one* post-deploy script per project, so it's common practice to have a single post-deploy script that calls other scripts using ":r".  If you do this, be sure to set the 'Build Action' for that script to 'Post Deploy', and the build action for the others to 'None'.

While this isn't comprehensive, I hope this is enough to get you started.  If there's anything else, just ask.  :o)

Tom

Answer 4

I've added a new Microsoft Connect item for allowing SqlCmd parameters to be part of identifiers.  I don't know how difficult this would be to implement, but it seems to me it would allow for some flexibility in these sorts of issues.  Anyway, go there and vote and/or comment if this is something you'd like to see in SSDT!

Tom

Answer 5

Hi Tom,

Thanks for your follow-up on this, I think i was trying to include it in a CREATE before, not in a post-deploy script. I knew something tripped me up on it ~18 months ago.

Thanks for this, in our product we still had the installer creating logins and associating them with database users (defined from the sqlproj). I would like the SSDT project to represent ALL db content.