SQLServer Default Database Changed

[Tim Carmichael]

On a SQLServer 2000 server, the default database for the user accounts was changed. This appears to have occurred for ALL users accounts, both network login names and SQLServer named accounts. Any idea how this can happen? Our DBA resigned Thanksgiving week, so there is no help onsite. Thanks in advance, Tim

[loyal ginger]

There are multiple ways to change the default database for the user accounts. You will need to retrieve a backup to restore the old default databases for all users. You may need to talk to the previous DBA about this issue. It is time consuming to find the cause by yourself. Was the previous DBA happy or not?

[David Mujica]

Use this statement to verify what you think happened ... select name,default_database_name,modify_date from sys.sql_logins If, indeed the changes were made, you will see when they happened and someone here might be able to work you through fixing it. You may want to start by changing your administrative passwords since your DBA left. Make sure to tighten up any remote access accounts, etc.

[Tim Carmichael]

David Mujica wrote:

Use this statement to verify what you think happened ...

Users could access the system last Thursday; on Friday, they couldn't. The default database was changed to a database that is used for sending e-mail messages, so, they neither need nor have access to it. The accounts (there are only a handful) have been manually updated.

David Mujica wrote:

select name,default_database_name,modify_date from sys.sql_logins

Since this is a SQLServer 2000 node, the listed table didn't work, but it gave me enough information to move forward. I used the following: select l.name, xdate1 as creation_Date, xdate2 as modify_date, l.dbid, d.name as defaultdb from sysxlogins l inner join sysdatabases d on l.dbid = d.dbid And that just opened up more questions... One of the accounts was created in 2007, updated a minute later and not touched after that, but, they have a default database set to a database that didn't exist at the time. As far as access rights, when someone leaves the company, their password is changed and remote access removed. Tim