question

srivivek avatar image
srivivek asked

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.
logintransfer
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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 + '] GO ' FROM syslogins
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
@srivivek Back in those days we were just happy to be able to get the users and their SIDs migrated.
2 Likes 2 ·
srivivek avatar image srivivek commented ·
Thank you, Magnus . But my question is why didn't they include in the REV-LOGIN script http://support.microsoft.com/kb/246133
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
I agree with TRAD. We made Master database restores instead when we moved to new servers.
0 Likes 0 ·
Tim avatar image
Tim answered
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.
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

srivivek avatar image srivivek commented ·
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
0 Likes 0 ·
srivivek avatar image srivivek commented ·
Is there any table where we can see default DB names of the LOGINS in SQL 2000
0 Likes 0 ·
Tim avatar image Tim commented ·
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?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.