Tuesday, May 5, 2009

Restoring a SqlServer DB causes login problems

I often see problems when migrating a SqlServer 2000 DB to SqlServer 2005, the database logins do not have a database server login, the db server doesn't let you modify the login throwing the error "Login name must be specified. (SqlManagerUI)".

Also often when doing a back up of a database then restore on a different DB server, the logins attached to the database at the DB level will not match the logis at the SqlServer level.

- With SqlServer 2000, you can simply delete the login at the database level, then recreate it at the SqlServer level, and re-assign access rights to your database
- With SqlServer 2005, this is trickier, as the UI doesn’t let you modify or delete the login at the database level.
The solution is to create the login at the SqlServer level, assign it access right to the database then run the command
sp_change_users_login 'auto_fix', myLogin

No comments:

Post a Comment