Disclosure: I am not an IT professional and I am certainly not a DBA.

That said, I can usually find my way around. I am following some instructions from a third party vendor to create a login, two databases, a user in each and ultimately a DSN to each.

Here are the steps:

1. Create a login (let's call it Test) with SQL Server Authentication and assign it a Public server role
2. Create two databases, both owned by the login we created (Test)
3. In each database, create a user with User Name and Login Name both set to Test and the default schema as dbo. Assign each user db_owner in Owned Schemas and Membership
4. Create a DSN to each database

If I follow these instructions, when I attempt to create the users I get an error: The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)

If I try creating a user with a different User Name (login name still Test), I get the same error.

If I try creating a user with a different Login Name, I get a different error: 'Test1' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

If I try skipping the users entirely and creating the DSN with the original Login, I get an error that login failed for user Test.

I have tried completely removing and re-installing SQL Server in case there was some duplicate name stored somewhere, but it didn't help.

So, I am not sure what else I can try. Clearly I am missing something, but I have no idea what.

CodePudding user response：

If the Login owns the database, it's mapped to the user dbo (which stands for "database owner") so you can't create another user for that login.

Normal practice is to have the databases owned by SA or your Windows Login, then you can create the user for the SQL Login and assign whatever permissions you need.