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
Tags: Script Default Database
Trackback from your site.
Setting and Changing Collation – SQL Server 2008 « Blog
| #
[...] to sql-articles.comRead more: http://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/#ixzz1pu2S8XW… Like this:LikeBe the first to like this [...]
VidhyaSagar
| #
Naveen,
I’ll check this out and get back to you.
balakiran
| #
Thanks man, Very simple & easy to understand !!!!
VidhyaSagar
| #
HI Manohar,
This product is not free, you need buy licenses. Refer http://www.xsql.com/order/product_licensing.aspx?productid=xSQLData link for details.
There are few products available as free but we didnt tested any.
Manohar
| #
Does this software come for free? Are there any other free data compare tools available for data compare?
Thanks
Manohar