Tuesday, July 31, 2012 7:43 PM
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.
- Edited by skrewed Tuesday, July 31, 2012 7:45 PM clarity
Thursday, August 02, 2012 8:14 AM
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!
- Proposed As Answer by Janet YeildingMicrosoft Employee, Owner Thursday, August 02, 2012 4:50 PM
Friday, August 03, 2012 3:27 PM
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'.
Friday, August 03, 2012 3:51 PM
- 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.
- 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".
- 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)
Tuesday, August 07, 2012 4:35 PM
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!
Tuesday, August 07, 2012 7:17 PM
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.