It can be a chore to move one SQL server database to another – if you want to save all the login and password information as well, that is. It’s easy enough to back-up and restore all the information on one SQL server database and transfer it to the new one, but what can be done to save the login and password info? It’s not as easy as simply right-clicking on each login window in SQL Server Management Studio, which allows them to be scripted-up using a feature called “Script Login As”. Where that will create logins within the default database, that wouldn’t include the passwords, as the script would merely create a random password for security purposes. (Note to the reader: This blog post won’t be of help to you if you have Windows Authentication logins rather than those of SQL Server, as the former are maintained by Windows.)
Since most business entities or individuals do not create lists of their various passwords, that makes it harder to be able to just copy and paste them into the newly-generated login script. The good news is that Microsoft has a useful stored procedure that will “script-up” logins so that they can use the original passwords to create the new ones. This only allows you to use a hashed form of the password for the target server, however, and does not allow you to extrapolate it for use in applications beyond the target server.
How Does Stored Procedure Work?
There are stored procedures which can help you re-generate stored passwords using SQL Server – one covers versions from 2005 up to 2012, and one type for earlier versions of SQL Server.
In the Microsoft support article dealing with SQL Server versions between 2005 and 2012, they include this important tip as part of their summary:
“In SQL Server 2005, the SID for a login is used to implement database-level access. A login may have different SIDs in different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem can occur if the two databases are combined from different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement.”
On the SQL Server help page that deals with earlier-than-2005 versions, they wrap-up with one of these summary remarks:
“The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.”
Need More Help Moving SQL Server Login Info?
If you need more information on how to safely and securely move SQL Server database logins and passwords, PNJ Technology Partners is the leader in providing IT consulting in Albany. Contact one of our IT experts at (518) 459-6712 or send us an email at info@pnjtechpartners.com for more info, and we will be happy to help.
