locked
Moving Dbs logins question about windows login RRS feed

  • Question

  • Hi All,

    I am moving 4 databases from 1 server to another and also copying the logins as well as permissions.

    After I move the DBS I copy the logins(both windows and sql) and then use sp_change_users_login 'update_one','sql login','sql login' to map the login tot he users which works fine

    but how do I do the same for my windows login as 1 login has numerous security roles  assigned to it.

    The individual DBs I guess have the users in it but the server login needs to have the ssame roles and permissions as the old server

    Any help

    Admina


    Ap
    Tuesday, July 7, 2009 2:26 AM

All replies

  • Unless you are moving between domains, you don't have to do anything to the logins mapped to Windows credentials.  You shouldn't need to do anything to roles or permissions, they are really just data within the databases that you moved.  So, as long as you recreate the logins, you are done.  The SQL Server standard logins will need to be remapped.

    SQL Server doesn't use the login name, it uses a SID.  When you create an account mapped to Windows credentials, the SID that is stored is the actual SID from Windows.  It is unique within the domain and will never change.  The reason that you might need to remap SQL Server standard logins is because when you create one of those, a SID is generated within the instance.  This is specific to teh instance and depends upon the order in which the logins are created.  When you add a user to teh database, it isn't the name that is stored, but the SID for the login.  Therefore, in order to correctly match up a database user to the corresponding SQL Server standard login, you may need to remap the SID if you created the logins in a different order.

    Now, if you are on SQL Server 2005 and higher, you can avoid all of the re-mapping business very easily.  The CREATE LOGIN statement allows you to explicitly specify a SID.  So, you use the SPID that the login had on the other instance along with the password hash when you create the login on the new instance.  At that point you are done.  The password matches and so does the SID.  The user doesn't have to change a password, the DBA doesn't ever have to know what the password is, and you don't have to do any of the re-mapping stuff because all of the SIDs match.


    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, July 7, 2009 4:18 AM
  • <<Now, if you are on SQL Server 2005 and higher, you can avoid all of the re-mapping business very easily.  The CREATE LOGIN statement allows you to explicitly specify a SID.>>

    ... and sp_help_revlogins (search) will even script out the existing login so you can run that script on the new server, same login and pwd.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, July 7, 2009 6:10 AM