Resetting user names for orphaned logins in SQL Server 2005

Sometime when we restore the database from a backup file in SQL Server 2005, after that some logins are not allowed access to database. error like can’t login user ‘null’ in the database occur when connecting using code.

This happens because while restoring the database backup the login was not linked with the user name it was associated in the database whose backup we are using. To login into the DB using that login you need to assign that login a username.

@ work I need to restore databases many times and use them to debug to solve the issue. So I used to get this problem every time I restore the backup. Earlier to solve this issue, I used to use brut force method to resolve this like deleting the troubled login from the “Database\Security\Users” and deleting the schema related to this login. Then adding the same login again to “Database\Security\Users” . This used to solve my problem.

Microsoft SQL Server 2005 provide a procedure to solve this issue, which is in fact the right way to it. sp_change_users_login this sp is intended to relink the orphaned login with their user name. following are the parameters to this procedure.

@Action: this parameter indicate what action to perform. it can be one of the 3 values

• Reports : Returns all the orphaned logins in the database
• Auto_Fix : Fixes the given login and sets the username to the user with the same name in database, if a user with same name is not found then a new entry in sys.database_principals table and the newly created entry is linked with the provided login.
• Update_One : updates the given login with the login name provided.

   1: use sample_db

   2: exec sp_change_user_login ‘Report’

The Report argument will return any orphaned users within the current database.

   1: use sample_db

   2: exec sp_change_user_login ‘Auto_Fix’, ‘sampleUser’

Above example displays usage of Auto_fix

   1: use sample_db

   2: exec sp_change_user_login ‘Update_One’, ‘sampleUser’, ‘sampleUser’

The above command will update the sampleuser users login to sampleUser.

Following are some references:

http://technet.microsoft.com/en-us/library/ms174378.aspx

http://blogs.msdn.com/blakhani/archive/2008/02/25/script-map-all-orphan-users-to-logins-else-create-login-and-map.aspx

Hope this helps

How to repair a SQL Server 2005 Suspect database

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

and you are done.

You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

Ha-P Querying…