how to change the default DB Name while transferring LOGINS

Question

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.

Answer 1

0

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

answered Mar 03 '11 at 11:19 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

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 '11 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 '11 at 12:52 PM Tim

I agree with TRAD. We made Master database restores instead when we moved to new servers.

Mar 03 '11 at 01:27 PM Magnus Ahlkvist

(comments are locked)

Answer 2

0

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.

answered Mar 03 '11 at 10:56 AM

Tim
31.5k ● 20 ● 31 ● 116

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 '11 at 11:04 AM srivivek

Is there any table where we can see default DB names of the LOGINS in SQL 2000

Mar 03 '11 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 '11 at 11:17 AM Tim

(comments are locked)

how to change the default DB Name while transferring LOGINS

Follow this question

Related Questions