Script Default Databases

Default Database is one of the most important parameter when we detach the database. If we don’t have the track of default db for logins then it might be a problem when you use an application. Consider you are planning to move a user database from X: drive to Y: drive and your application login has this database as default db. Without noticing this if you detach the database then the application login will point either to null, so you need to map the db again to the login, hence after attaching the database and if you work with application then you will be ended with the below error

Cannot open user default database. Login failed
Login failed for user ‘xxx’. (Microsoft SQL Server, Error:4064)

To avoid this we can script default db for all the logins using the below script which will provide the output as T-SQL statement. You need to execute the script before detaching the db and after attaching it successfully execute the output from the previous script execution which will again map default databases to the respective logins!!

Script:

/*
Script to detach all user databases
Written by Vidhya Sagar
www.sql-articles.com
*/
DECLARE @name VARCHAR(100), @dname VARCHAR(75), @all VARCHAR(500)
DECLARE dbname CURSOR FOR SELECT name FROM master..syslogins
OPEN dbname
FETCH dbname INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @dname=dbname FROM master..syslogins WHERE name=@name
SET @all='sp_defaultdb '+''''+@name+''','+''''+@dname+''''+CHAR(10)+'go'
PRINT @all
FETCH dbname INTO @name
END
CLOSE dbname
DEALLOCATE dbname

Output:

sp_defaultdb 'test','sagar_25'
GO