0

I'm planning the migration of some SQL Server 2008 R2 databases between two servers.

The proposal is to follow Method 3 in this article: http://support.microsoft.com/kb/918992 to transfer logins across after the databases have been migrated.

This mostly looks OK to me but one concern is if any of these logins already exist on the target server. If this is the case would anything additional need to be done and if so what?

[My understanding's a little hazy here - is it possible that users in the restored database would still be orphaned since the logins they referenced have different SIDs on the new server?]

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208

1 Answers1

1

That script generates login scripts rather than executing the statements directly, so you can review them, and just run the scripts for users that don't already exist on the target.

The script will generate new SIDs dependant on whether the new login needs a new one - it depends on the type of login.

From MSDN on the subject:

The source of the SID depends on how the login is created. If the login is created from a Windows user or group, it is given the Windows SID of the source principal; the Windows SID is unique within the domain. If the SQL Server login is created from a certificate or asymmetric key, it is assigned a SID derived from the SHA-1 hash of the public key. If the login is created as a legacy-style SQL Server login that requires a password, the server will generate a SID.

I don't think you need to worry about that unless you're using the sys.server_principals table for something special.

From the comments below, Steve wanted to create the logins by script, and detach and re-attach the databases on the new server. The concern was that these logins would now be orphaned - i.e. attached to a login on the original server with the same name as the one on the new server, but with a different SID.

Yes, this would be a problem. To sort this, you'd need to visit this MSDN article. To summarise:

To detect orphaned users, execute the following Transact-SQL statements:

USE <database_name>;
GO; 
sp_change_users_login @Action='Report';
GO;

The output lists the users and corresponding security identifiers (SID) in the current database that are not linked to any SQL Server login. For more information, see sp_change_users_login (Transact-SQL).

To resolve an orphaned user, use the following procedure:

The following command relinks the server login account specified by with the database user specified.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
   @LoginName='<login_name>';
GO

As Steve points out, sp_change_users_login is depreciated and the preferred alternative is to use ALTER USER:

ALTER USER <database_user>
WITH LOGIN <login_name>

MSDN link

Bridge
  • 29,818
  • 9
  • 60
  • 82
  • Thanks for your answer. Just to explain a bit more, it's the SQL Server Authentication logins I'm a bit worried about. E.g. let's say SOURCE_SERVER has an "sqlserverauth" login and a users in SOURCE_DATABASE is linked to this "sqlserverauth" login. What will happen to this users when SOURCE_DATABASE is migrated to TARGET_SERVER, which also happens to have an "sqlserverauth" login (presumably with a different SID?) – Steve Chambers Jan 29 '13 at 11:00
  • @SteveChambers Note that the article and script linked is only for copying logins (server level access), not for copying users (database level access). So nothing will happen to them, they won't exist on the destination server! The script does not "move" logins either - just create a script to copy the original (they all still exist on the source server). – Bridge Jan 29 '13 at 11:01
  • In reply to the comment above, surely the users would still exist when the databases are migrated but would be orphaned? It's the unorphaning part where the "parent" login uses SQL Server Authentication that I'm looking for... – Steve Chambers Jan 29 '13 at 11:03
  • @SteveChambers See [this SO question](http://stackoverflow.com/questions/1134319/difference-between-a-user-and-a-login-in-sql-server) for a brief explanation of the difference if you're unsure of the differences :-) – Bridge Jan 29 '13 at 11:03
  • Thanks @Bridge - I think I'm OK with the difference between logins and users. To be clear it's the users of type "SQL user with login" where login is of type "SQL Server authentication" that I'm talking about. – Steve Chambers Jan 29 '13 at 11:06
  • After running this, Logins would now exist on source server _and_ target server (different SIDs), users would only exist on source database matched to the original source server login. Users would need to be recreated on target server, matched to the login copied across from the script either way. – Bridge Jan 29 '13 at 11:07
  • To explain, I'll be migrating the databases by either detaching and copying the files across or doing a backup and restore. Either way, my understanding is the same set of users would still exist after the migration - they would just be orphaned if the logins don't exist. – Steve Chambers Jan 29 '13 at 11:10
  • 1
    @SteveChambers I get you now! You'll need to look at [this](http://msdn.microsoft.com/en-gb/library/ms175475%28v=sql.105%29.aspx) MSDN article - which gives a script to "re-link" the orphaned users to the logins of the same name on the new server! – Bridge Jan 29 '13 at 11:12
  • @SteveChambers I've edited my answer to include the parts of that article you were looking for. – Bridge Jan 29 '13 at 11:21
  • Thanks @Bridge. Looks good although sp_change_users_login is deprecated. If you could modify your answer to use ALTER USER instead I'll gladly accept. Syntax looks to be something like: ALTER USER username WITH login loginname – Steve Chambers Jan 29 '13 at 11:39
  • @SteveChambers So it is - I shouldn't be surprised at MSDN, their documentation is all over the place. I've edited to include that. I might submit that documentation issue on connect if I get time later on. – Bridge Jan 29 '13 at 11:48