Creating User Through SQL statements

[G_Waddell]

Hi,

I'm writing a VB.net application that runs off a SQL database. If the application can not connect to the database or the connection has not been set up I'm giving the user the option to create the database on the SQL server instance of their choice I then create the database and it's tables, dedicated user for the application to use and Stored Procedures. All this worked fine when testing yesterday including the user creation BUT for some reason, the User creation is throwing an error today. The Scripts I use to set up the user are as follows:

I run this Script on the master when logged in as an Server admin user.

CREATE LOGIN MyAppUser WITH PASSWORD ='UserPassword', DEFAULT DATABASE = MyAppDB

This sets up the SQL Login I then run the following on the actual database to add the SQL Login to the Database User list:

CREATE USER MyAppUser FOR LOGIN MyAppUser

Unfortunately, I am now getting an error telling me the user, group or role already exists in the current database but if I look in the SQL Server, I can only see the user under the Server logins not under the Application Database Users. I'm going to try removing DEFAULT DATABASE setting but I understand this then just defaults to master.

[pritaeas]

Sounds like MyAppUser is the problem. Is it allowed for a login and a user to have identical names?

[indiahelly]

Nice post.. Thanks for sharing.

[G_Waddell]

Pritaeas,
Yes, it is allowed, the User is Database specific and the Login is Server specific. I am mapping the SQL server Login to a SQL user on the database.

Anyway figured out what happened, - a simple mistake, I modifed my code and accidently took out the part where I disconnect from the master DB and connect to my new application DB.

So I was creating the Application DB then creating the user in the master DB not the application DB hence although I deleted my application DB the code was trying to put everything into the master DB....