Sql-server – Can’t create user in SQL Server

schemasql-server-2008

Hi guys when I try to create user for my database I always encounter this kind of error.

enter image description here

This is the sequence of task I done.

I manage to create new login using windows authentication of SQL then I use that login to access SQL Server again then I create my database and set the owner of that database to the login that I was created. After creating the database, I try to create new user and assign the login I use but I was halted by this error.

Best Answer

It is normal you cannot associate two different user to a single login for a database. You must understand the difference between a login and a user.

A login authenticate you on the server. Once you are authenticated, the login will impersonate a user to access the specified database of the connection string.

So a login can have only one user per database.

Sample : login [myDomain\myUser] has access on SqlServer. This login can have a user per database : userA for databaseA, userB for databaseB.

If you want to add an other user to the login for the same database, you have to drop the first user. Instead, you have to create a different login for the second user.

Hope this help you.

Related Solutions

Sql-server – Local user login access in sql server 2008

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:

Error: 18456, Severity: 14, State: 8.

The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid 
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required

Sql-server – SQL Server Management Studio Change User Server Roles to sysadmin

You've explained that you don't have the SA password and you're not an administrator. In that case, you're going to have to hack your way around it.

See Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again. by Argenis Fernandez.

That post explains how to impersonate the NT AUTHORITY\SYSTEM login using PsExec (or creating a Windows Scheduled Task running under the SYSTEM account) to create a new login for yourself. Then, you can log in via that new login, and grant your Windows account SA permissions.

Best Answer

Related Solutions

Sql-server – Local user login access in sql server 2008

Sql-server – SQL Server Management Studio Change User Server Roles to sysadmin

Related Question