how to change the default DB Name while transferring LOGINS

Hi, I have transferred logins from one SQL server 2000 to other SQL 2000 using the REV_LOGIN script provided by the Microsoft. everything went good ,but when i checked the login properties in the new server i found the default database as MASTER where in the previous server it is different. how to change this Default database name same as it is in primary server. I can't do manually because i got more than 1500 logins. please help me out.....

thanks in advance.
more ▼

asked Mar 03, 2011 at 10:54 AM in Default

srivivek gravatar image

519 41 42 43

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

If you have already transferred the logins, and now want to update the default database in the new server, I would run a query in the old server to create a script that you can run in the new server.

I did this in SQL Server 2008 R2. Perhaps it'll work without too much modification in SQL Server 2000.

--Run this script in the old server. 
--Get the output and run in the new server's master database.
SELECT 'ALTER LOGIN [' + loginname + '] WITH DEFAULT_DATABASE=[' + dbname + ']
FROM syslogins 
more ▼

answered Mar 03, 2011 at 11:19 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

Thank you, Magnus . But my question is why didn't they include in the REV-LOGIN script http://support.microsoft.com/kb/246133
Mar 03, 2011 at 12:50 PM srivivek
@srivivek Back in those days we were just happy to be able to get the users and their SIDs migrated.
Mar 03, 2011 at 12:52 PM Tim
I agree with TRAD. We made Master database restores instead when we moved to new servers.
Mar 03, 2011 at 01:27 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Did the default database from the old server exist on the new server? If the database didn't exist it wouldn't be able to set it as the default. Typically most scripts I have used to migrate users also includes an ALTER LOGIN script that sets the default database.
more ▼

answered Mar 03, 2011 at 10:56 AM

Tim gravatar image

36.4k 39 41 139

the DB exists in the new server. But when we check the script,we don't see any variable storing the DEFAULT DB. http://support.microsoft.com/kb/246133
Mar 03, 2011 at 11:04 AM srivivek
Is there any table where we can see default DB names of the LOGINS in SQL 2000
Mar 03, 2011 at 11:07 AM srivivek
I am reviewing the kb article. I executed it on my test environment and this script does not include setting the default DB. Have you tried this in DTS?
Mar 03, 2011 at 11:17 AM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 03, 2011 at 10:54 AM

Seen: 2479 times

Last Updated: Mar 03, 2011 at 10:54 AM