locked
Not able to create login in SQL Server 2008 RRS feed

Answers

  • 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

    • Marked as answer by ihvarrived Monday, January 7, 2013 10:36 AM
    Monday, January 7, 2013 10:36 AM

All replies

  • 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

    Monday, January 7, 2013 7:56 AM
  • 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

    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




    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 details
    Monday, January 7, 2013 8:04 AM
  • updated error details
    Monday, 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 WINDOWS

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

    Please report the outcome.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, 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.se
    Monday, 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
    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
  • 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

    • Marked as answer by ihvarrived Monday, January 7, 2013 10:36 AM
    Monday, January 7, 2013 10:36 AM
  • I'm glad to hear that it worked out!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 7, 2013 11:23 AM