x

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 '11 at 10:54 AM in Default

srivivek gravatar image

srivivek
519 39 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 + ']
GO
'
FROM syslogins 
more ▼

answered Mar 03 '11 at 11:19 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

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)
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 '11 at 10:56 AM

Tim gravatar image

Tim
35.5k 32 41 138

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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x81
x4

asked: Mar 03 '11 at 10:54 AM

Seen: 1879 times

Last Updated: Mar 03 '11 at 10:54 AM