A very common issue that’s raised in the newsgroups relates to SQL Server logins that need to be moved between servers. When you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password.
The problem then comes when you restore a database from another server. You can’t access it. If you to to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn’t show it.
I’ve gotten around this problem in the past by specifying the SID value when creating the login in TSQL. It is an optional parameter. If you provide the same value as on the other server, you don’t have the problem.
The standard answer for this has been to use sp_change_users_login. It has an option to list any mismatched logins and database users ie: those with the same names but different SIDs. It then has an option to “fix” it. The way it fixes it is to update the SID in the database user to match the login.
In SP2, another option to help with this has appeared. Laurentiu Cristofer has documented it here: http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx even though it hasn’t hit books online yet. It is a variation of the ALTER USER statement that allows you to specify WITH LOGIN to fix the problem. It also lets you do this for both SQL and Windows logins.
The problem I see with this all is that it still just propagates the problem. It’s not the database SID that needs fixing, it’s the Login’s sid. Then there wouldn’t be a problem with copying the databases around. The most common scenario I see is the following:
1. A database is restored from another server (or a reinstalled server).
2. The logins that use the database need to be recreated.
The option I’d like to see is:
CREATE LOGIN some_login WITH PASSWORD = ‘some_password’, SID FROM DATABASE some_existing_database
This would avoid the problem in the first place and then give you a database you could copy around as needed. If you think that sounds interesting, vote for it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=269442