Not able to create login in SQL Server 2008

Question

i am not able to create login in SQL Server 2008 with Windows / SQL Server Authentication.

Where as the login doesnot exist in the db. Also, checked syslogins and login is not present there.

Loged in to SSMS -> Security -> Login -> New User -> provide details -> OK (Error comes here. Details below.)

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for Login '<domain\user'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal '<domain\user' already exists. (Microsoft SQL Server, Error: 15025)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=15025&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Answer 1

Hello,

"Not able" means you don't know the syntax for it or do you get an error message (which one)?

---

Olaf Helper

Blog Xing

Answer 2

updated the details

Answer 3

BOL: "CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';
GO"

http://msdn.microsoft.com/en-us/library/ms189751.aspx

ALTER ANY LOGIN permission on the server required or must be in securityadmin/sysadmin fixed server roles.

>Where as the login doesnot exist in the db

In the db you create a user.

On the server you create a login.

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

by which way you are creating login. i guess your using object explorer.

can you provide the steps you are following..?

Answer 5

updated error details

Answer 6

updated error details

Answer 7

So run this from a query window:

SELECT * FROM sys.server_principals WHERE name = 'domain\user'
SELECT suser_sid('domain\user')
CREATE LOGIN [domain\user] FROM WINDOWS

First make sure that you are connected to the right server.

Please report the outcome.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

SELECT * FROM sys.server_principals WHERE name = 'domain\user' : result is Nothing
SELECT suser_sid('domain\user'): Got an SID
CREATE LOGIN [domain\user] FROM WINDOWS: got error:

Msg 15025, Level 16, State 2, Line 1
The server principal 'domain\user' already exists.

Answer 9

OK. Now you do:

SELECT * FROM sys.server_principals WHERE sid = suser_sid('domain\user')

It seems that this user has been renamed in the AD, and the above should return the old name.

In theory you could do

ALTER LOGIN [domain\oldusername] WITH NAME = [domain\user]

to rename the user in SQL Server as well, but I would recommend that you verify with the Windows admin that there actually has been a name change. You should also review what names the user has in SQL Server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

Erland

A question, How come the suser_sid return a value when the user is no more there in the same name?

Thanks

Murali

Answer 11

A question, How come the suser_sid return a value when the user is no more there in the same name?

Because it goes out and ask Windows. Not that I know that it does, but it has to; keep in mind that a user may have access to SQL Server through a group, in which case the SID for the Windows user is not stored in SQL Server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

SELECT * FROM sys.server_principals WHERE sid = suser_sid('domain\user') does not return any user name, but SELECT suser_sid('domain\user') is returning a SID. Is there a way to delete this SID?

Answer 13

Thanks to Erland. Showed the way. Here's the resolution:

- SUSER_SID('<domain>\<user>') gave the sid

- select * from sys.server_principals where SID = SUSER_SID('<domain>\<user>')

  gave me the user name with same sid which was causing the problem. deleted that user and created new one and magic :D

Answer 14

I'm glad to hear that it worked out!

---

Erland Sommarskog, SQL Server MVP, [e-mail]