Answered by:
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)
------------------------------
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
------------------------------- Edited by ihvarrived Monday, January 7, 2013 8:04 AM
Monday, January 7, 2013 7:47 AM
Answers
All replies
-
-
updated the details
Monday, January 7, 2013 7:57 AM -
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
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
- Edited by Kalman Toth Monday, January 7, 2013 8:02 AM
- Proposed as answer by Rohana K Amarakoon Wednesday, June 4, 2014 6:45 AM
Monday, January 7, 2013 7:58 AM -
by which way you are creating login. i guess your using object explorer.
can you provide the steps you are following..?
Monday, January 7, 2013 8:01 AM -
updated error detailsMonday, January 7, 2013 8:04 AM
-
updated error detailsMonday, January 7, 2013 8:06 AM
-
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 WINDOWSFirst make sure that you are connected to the right server.
Please report the outcome.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seMonday, January 7, 2013 8:21 AM -
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.Monday, January 7, 2013 8:34 AM -
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, esquel@sommarskog.seMonday, January 7, 2013 9:10 AM -
Erland
A question, How come the suser_sid return a value when the user is no more there in the same name?
Thanks
Murali
Monday, January 7, 2013 9:13 AM -
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, esquel@sommarskog.se- Proposed as answer by Rohana K Amarakoon Wednesday, June 4, 2014 6:45 AM
Monday, January 7, 2013 9:52 AM -
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?
- Marked as answer by ihvarrived Monday, January 7, 2013 10:36 AM
- Unmarked as answer by ihvarrived Monday, January 7, 2013 10:36 AM
Monday, January 7, 2013 9:55 AM -
-