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…

Truncating Transaction Log in SQLServer 2000

Few days back I had a situation when size of my database transcation log was growing too  fast (at a point it reached 60 GB). so I had to find some way to truncate it to some minimal size. After lot of googling, I found how to truncate the transaction log.

Run these two commands in Query Analyser.

1. BACKUP LOG <<DBName>> WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE(<<DBName>>_log, <<Desired transaction filesize>> )

Remarks :

• DBCC SHRINKFILE applies to the files in the current database. If you try to  run this command in a different DB then the DB you want to shrink, SQL Server will give following error

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘<<DBName>>_log’ in sysfiles.

• SQL Server uses <<Desired transaction filesize>> to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKFILE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file’s target size, the file is successfully truncated and DBCC SHRINKFILE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file. (Remarks are taken from MSDN)

References…..

1. Microsoft Knowledge Base (KB272318)
2. DBCC SHRINKFILE

Ha-P Querying