none
Moving SQL Server Logins RRS feed

  • Question

  • HI ,

     We are planning to upgrade our SQL Server 2008 R2 sp2 to SQL Server 2012 SP1.   It is an out of place upgrade.  I have already tried the below KB article. But when I am exec sp_help_revlogin it is throwing errors.  Can anyone let us know how to transfer SQL Server logins  from old server to new server.

    KR article:

    http://support.microsoft.com/kb/246133

    Regards,

    Varun

    Wednesday, January 21, 2015 11:53 AM

Answers

All replies

  • Hello Varun,

    There are 2 Versions of the script avaiable and the script from your link throws Errors on newer SQL Server; use this one instead: https://support.microsoft.com/kb/918992


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Raje14 Wednesday, January 21, 2015 12:26 PM
    Wednesday, January 21, 2015 12:01 PM
    Moderator
  • Hi Varun,

    There is new support article for this.

    http://support.microsoft.com/kb/918992/en-gb

    What you posted applied to SQL Server 2000.

    Plus what error you got  ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by Raje14 Wednesday, January 21, 2015 12:26 PM
    Wednesday, January 21, 2015 12:01 PM
    Moderator
  • Hello Varun,

    There are 2 Versions of the script avaiable and the script from your link throws Errors on newer SQL Server; use this one instead: https://support.microsoft.com/kb/918992


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thanks Olaf Helper  it fixed my problem..

     
    Wednesday, January 21, 2015 12:26 PM
  • Hi Varun,

    There is new support article for this.

    http://support.microsoft.com/kb/918992/en-gb

    What you posted applied to SQL Server 2000.

    Plus what error you got  ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Thanks Shanky_621..
    • Marked as answer by Raje14 Wednesday, January 21, 2015 12:27 PM
    • Unmarked as answer by Raje14 Wednesday, January 21, 2015 12:27 PM
    Wednesday, January 21, 2015 12:27 PM
  • Hello Varun,

    There are 2 Versions of the script avaiable and the script from your link throws Errors on newer SQL Server; use this one instead: https://support.microsoft.com/kb/918992


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thanks Olaf Helper  it fixed my problem..

     

    Hi,

    the above script creates the logins in sql server. Is there any script to copy the permissions to those users.

    Thanks,

    Varun

    Wednesday, January 21, 2015 2:18 PM
  • Hi Varun,

    There is new support article for this.

    http://support.microsoft.com/kb/918992/en-gb

    What you posted applied to SQL Server 2000.

    Plus what error you got  ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Thanks Shanky_621..

    Hi,

    the above script creates the logins in sql server. Is there any script to copy the permissions to those users.

    Thanks,

    Varun

    Wednesday, January 21, 2015 2:19 PM
  • Hi Varun,

    the "permissions" doesn't need to be copied over because the connection between Login and database is based on the SID. That's the reason why you have copied the logins from server 1 to server 2.

    You can check the SID of the logins and compare it with the SID in the database users:

    -- Logins
    SELECT SID, name FROM sys.server_principals;
    
    -- Users
    USE <YourDB>;
    GO
    
    SELECT SID, name, FROM sys.database_principals;

    All security settings stayed IN the database and will not changed/removed by the simple backup - restore!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Wednesday, January 21, 2015 2:24 PM
  • Is there any script to copy the permissions to those users.

    Hello Varun,

    The permissions are stored in each database, so if you move the databases then you will have the permission exact as before.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 21, 2015 2:33 PM
    Moderator
  • Is there any script to copy the permissions to those users.

    Hello Varun,

    The permissions are stored in each database, so if you move the databases then you will have the permission exact as before.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thanks Olaf.. how about server level permissions and mapping of server login to databases.. please clarify..

    Wednesday, January 21, 2015 5:29 PM
  • Varun,

    To copy server level permission you need to do one more step

    http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html

    --Prashanth

    • Marked as answer by Raje14 Thursday, January 22, 2015 2:30 AM
    Wednesday, January 21, 2015 6:06 PM