Member Avatar for 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.

  • 3 Contributors
  • 3 Replies
  • 344 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by G_Waddell
Member Avatar for pritaeas

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

Edited by pritaeas
Member Avatar for indiahelly

Nice post.. Thanks for sharing.

Member Avatar for 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....

commented: Thanks for sharing. +13
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.