Archive for July, 2007

Fixing Database Logins After A Restore

For several years now I've been moving development databases between SQL Servers using backup and restore. When you restore the database on the target server, the logins for the database are invariably broken with the database user having an empty login name, meaning that they cannot log in to the database. My usual fix is to delete the database user and re-add it. Paul Hayman however pointed out a useful stored procedure to fix broken logins:

sp_change_users_login 'Auto_Fix', 'username'

where username is the name of the account to fix.

The Auto_Fix option will attempt to match the broken login with an existing user with the same name.

More information on this can be found in the MSDN documentation. Specific things to note are that it only works with SQL Server and not Windows logins, and that you must be a member of the sysadmin fixed server role for it to work.

Add comment Permalink July 17th, 2007 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post


Calendar

July 2007
M T W T F S S
« May   Sep »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Posts by Month

Posts by Category

RSS Feeds Feeds

Visitors

Visitor Map