locked
SQL 2005 logins after moving databases to new server RRS feed

  • Question

  • I have an existing server that is running SQL Server 2005.  I received a new server and I installed SQL 2005 on it.  I backed up the databases on the original server and I restored them on the new server.  I am using mixed mode authentication.

    I noticed that the logins showed up on the new server under each individual database.  However, when I go to SQL Management Studio under the Security tab, I don't see any of the logins listed there like they are on the old server.  When I tried to add a SQL authentication login manually, I get an error message

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

    Create failed for User 'challyk'.  (Microsoft.SqlServer.Smo)

    ADDITIONAL INFORMATION:

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

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

    User, group, or role 'challyk' already exists in the current database. (Microsoft SQL Server, Error: 15023)

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

    I tried to follow the following article to copy the logins from the old server to the new one http://support.microsoft.com/kb/918992/ 

    but when I go to SQL Management Studio, click on New Query, then I try to run the script from the link, I get an error message:
    "Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 59
    'LOGINPROPERTY' is not a recognized function name."

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

    What's the best way to get all the logins to show up under the Security tab?

    Tuesday, October 13, 2009 5:09 AM

Answers

  • Hi GilbertF

    Don't export the results to a text file (unless you use a tab-delimited output)

    The export to CSV is inserting extra commas, and this is causing your SQL to fail.

    If you want to save to file, execute the first query with text-output (ctrl-T), and then copy and paste the result to file.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Monday, October 19, 2009 11:01 AM

All replies

  • Hello,

    Please use the Copy Database Wizard. It will create logins on the destination server.

    http://msdn.microsoft.com/en-us/library/ms188664(SQL.90).aspx

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

     

     

    Tuesday, October 13, 2009 11:30 AM
  • Hi Gilbert

    You need to script the logins into the new server to preserve the SIDs.

    You only see the users in the database - the logins are stored in master. That's why you cannot see them. They are not created when you restore a user database.

    For the Windows Logins, you can just add them manually, since the SIDs remain the same - there will not be a mismatch. This might be feasible if you have a small number of users. If not you'll need to script them. For the SQL Logins, you must script them to preserve the SID information (or use the wizard as Alberto has suggested)

    On the original server run the following SQL in master, and execute the results on your target server to create the new logins

    -- For SQL Logins
    
    select 'CREATE LOGIN  [' + l.name + '] WITH PASSWORD = ', (CAST(l.password as varbinary(256))), ' HASHED, SID = ', CAST(l.sid as varbinary), ', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
     ' + 'GO
     ' from master..syslogins l
     where l.name <> 'sa' and l.isntname + l.isntgroup = 0
    
    
    -- For Windows Logins
    
     select 'CREATE LOGIN  [' + l.name + '] FROM WINDOWS
     ' + 'GO
     ' from master..syslogins l
     where l.isntname + l.isntgroup = 1
    

    HTH

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, October 13, 2009 11:42 AM
  • I was able to execute the query on the original server but I'm getting an error message when trying to execute the results on the target server.  I exported the results to a TXT file and I'm getting this:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ','.

    This is what I'm running:

    CREATE LOGIN  [Brenda] WITH PASSWORD = ,0x01008E1...., HASHED, SID = ,0xF8CB....,, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
     GO

    I didn't post the entire password hash or SID.
    Friday, October 16, 2009 8:13 PM
  • I think the commas are wrongly used.

    USE with format with proper commas at the right place,

    CREATE LOGIN  xxx WITH PASSWORD =     0x0100868026413632397F938FE3B607CDF3294FA55CE569FF5C11     HASHED, SID =   0x65253DC7708DD4CA953493DD06A9DB3    , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    Thanks, Leks
    Saturday, October 17, 2009 7:55 AM
  • Hi GilbertF

    Don't export the results to a text file (unless you use a tab-delimited output)

    The export to CSV is inserting extra commas, and this is causing your SQL to fail.

    If you want to save to file, execute the first query with text-output (ctrl-T), and then copy and paste the result to file.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Monday, October 19, 2009 11:01 AM