How To Restore A SQL User After a SQL Database Restore

Jul 13, 2006
by:   Tim Stanley

You backed up your Microsoft SQL Database and restored it on another system, but the user id's you've used before can not access the database.  This article is a summary of how to quickly configure the SQL Server database so that your previous user id's can access the database properly.

 

Why Does This Happen?

When transferring a database to a new server using backup and restore or when detaching and re-attaching the database, the links for the database users are broken. The SQL User information stored in the "master" database in the original server is usually not moved. This information lives in the "syslogins" table in the master database.

Find The Missing Users

Unless you already know the all the user id's, you'll need to find which ones are missing.

EXEC sp_change_users_login 'Report'

Create The User Account

To automatically create and restore the SQL User Account:

EXEC sp_change_users_login , UserName', NULL, 'password' 

To create the SQL User Account (you'll need to restore the SQL user in the next step):

CREATE LOGIN 'UserName' WITH PASSWORD= 'password'

Restore The SQL User

To restore the SQL User Account:

EXEC sp_change_users_login 'Update_One', 'UserName', 'NewUserName'

References

Add to favorites Send to a friend Digg It! DZone It! StumbleUpon Technorati Reddit Del.icio.us NewsVine Furl BlinkList