Login default database

[bertieuk]

Hi

I am migrating from sql 7 to 2000. I have moved the user names and login details.

I then detached a couple of databases and moved them to the new 2000 server.....All looks ok.

When I look at logins (under Enterprise Manager) the user default databases are all set to master, whereas on the sql7 system each user defaulted to their individual databases. Is it important to move them to their database. Even though I am SA it asks me for the database password which I dont have cus they belong to customers.

I am confused about being able to change the default database.

Any help Thanks SI

 

[mrdenny]

The users accounts will have defaulted to the master database because the database that was supose to be there default database didn't exist when you created there account. To set the default database the database they will be using needs to exist.

Last time I did something like this for a customer system I ran a query on the old server, which generated a script to run on the new server which set the default databases for all users on the system. Then detach the databases and move them over and attach them. Now run the script that was created on the other server and it should reset all the user accounts correctly.

Here is the script to run on the old server.

/*This is from SQL 2000, but it should work on SQL 7*/
select 'exec sp_defaultdb @loginame=''' + sysxlogins.name + ''', @defdb+''' + sysdatabases.name + ''''
from sysxlogins
join sysdatabases on sysxlogins.dbid = sysdatabases.dbid

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bertieuk]

Thanks denny

A slight mod was needed after @defdb to change the + to an = but the output worked a treat.

Can you tell me what the real purpose of setting the default login database and the consequence of not setting it or leaving it set to master.

Many thanks for the help
Si

 

[mrdenny]

Oh, good point. Didn't see that. Good catch.

Denny

--Anything is possible. All it takes is a little research. (Me)