Fixing Database Logins After A Restore

July 17th, 2007 Adrian Banks

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.

Entry Filed Under: MSDN, SQL Server


Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

Leave a Comment

Required

Required, hidden

Please enter the following word: magic word

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Subscribe to the comments via RSS Feed


Calendar

March 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Most Recent Posts

Related Posts

RSS Feeds Feeds

Visitors

Visitor Map