SSDT logins and users

Question

Hi

I "reversed engineered" new .sqlproj (using SSDT) from an existing production database.
During this phase several scripts were created under "Security" folder.
Besides "Permissions.sql" and "RoleMemberships.sql" for each DB user 2 sql scripts are created for each of CREATE USER and CREATE LOGIN.
So I have:

username_fr.sql -- CREATE USER [username_fr] FOR LOGIN [username_fr];
username_fr_1.sql -- CREATE LOGIN [username_fr] WITH PASSWORD = N'ommited', SID = 0x123...., DEFAULT_DATABASE = [MyDb], DEFAULT_LANGUAGE =[us_english];

Now when I do a build (no errors) I try to run Debug so that db would be created under (localdb) instance for testing purposes I get an error that the database does not exist when users and logins scripts run...

Creating [username_fr]...
C:\Visual Studio 2010\Projects\Solution1\Database1\bin\Debug\DbProj.sql(48,1): Error:  SQL72014: .Net SqlClient Data Provider: Msg 15010, Level 16, State 1, Line 1 The database 'MyDb' does not exist. Supply a valid database name. To see available databases, use sys.databases.

• Is there something I am doing wrong or missing?
• Why did SSDT create a pair of scripts per db user? why not Logins.sql and Users.sql and put creation of all users and logins into 2 scripts (just like Permissions.sql and RoleMemberships.sql - it seems odd it didn't combine them)

Thanks

Z

Answer 1

Hi Z,

It appears that the reason your login creation is failing on F5 deployment is because the MyDB database, which is set to the default database for your login, does not exist on your localdb debug instance. When creating the login, the DEFAULT_DATABASE (if it is specified) must exist. If you were to try and execute the same create login/user sequence that you are describing against another 2012 instance that doesn't have MyDB, you would also run into the same issue.

Secondly, what do you mean by "create a pair of scripts per db user"? Are you simply referring to the fact that the user and the associated login are brought into the project as separate files?

Thanks,

Adam

---

Adam Mahood - Program Manager - SQL Server Data Tools

Answer 2

Hi

I spend some time testing this and I got it to work. First of all, I am using Visual Studio 2010 with latest SSDT tooling which I am guessing installed LocalDB.

When I created a project from existing database, which has 3 users, the tooling generated 6 separate files (2 files per user, 1 for creating login and 1 for creating a user). However, for permissions and role memberships it only created a single file for each. I decided to test by grouping login creation into Logins.sql and user creation into Users.sql and this works just fine.

Please note that I was able to F5 and the database was created along with required logins and users, non of which existed.
Then I tried specifying to drop DB every time and that worked as well....

So I am not sure if putting all logins into single .sql resolved my issue or not but it works (I did have to separate each CREATE LOGIN with a GO)

To confirm the answer to your question:

Secondly, what do you mean by "create a pair of scripts per db user"? Are you simply referring to the fact that the user and the associated login are brought into the project as separate files?

Yes that is what happened (for 3 db users, 6 files were created) - My question is why?  Why not single Logins.sql and Users.sql

Answer 3

Hi Z,

Glad you were able to solve your original F5 deployment problem.

With regards to object grouping, this is simply part of our default reverse engineering behavior. Obviously, the current behavior is to separate out logins and users into separate files. I will be sure to make note of your desire to see logins and any users mapped to those logins in one file.

Thank you for the feedback, and if you have any other issues, please let us know.

Thanks,

Adam

---

Adam Mahood - Program Manager - SQL Server Data Tools