Cannot login after default database is deleted

Question

Cannot login after default database is deleted

mweaver

Old Hand

Points: 336
More actions
March 2, 2004 at 10:28 am

#101896

Has anyone ever had a problem when the default database for a particular login gets deleted (SQL Server 2000 SP3)?
I have a Windows User login (machine Administrator) whose default database was deleted. The next time the user attempted to login, the login failed because the default database was inaccessible. As a workaround, I had a different Administrator login and change the default database for the first login - but is there some way the first user could change his own default database (even though he can't login ...)?
What if I didn't have any other Aministrator logins and this happened to 'sa'? Would I be out of luck?
This seems like something that SQL Server should take care of - automatically assigning a different default database when the default database is deleted.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

The default database for sa should be set to master which should (hopefully) always be there. It would be cool if a user could change their default database but then if they are connecting via OLEDB or ODBC they can! Its part of the connection string. If you are referring to EM or QA and this is a login with server privileges then their default db should be master. If they do not have server privileges they need to pick which database they want. It would be neat if SQL Server would look at other databases and randomly pich a different one assuming there exists a second one to which they have access and only failing if the login has no access to other databases, but this isn't available.

Francis

gljjr SSChampion Points: 10080 More actions · Answer 2

I've had this problem. Since then I make sure that my login is ALWAYS set to default to the master database. In my case the database in question became suspect due to lack of hard drive space for the log file. I would have been in a world of hurt if I didn't have a sa account to fall back on! Of course I could have created a VB app to connect via ODBC as stated above and then changed my default DB so I could get in (I haven't tried it but I see no reason it wouldn't allow you to do that).

As for SQL Server automatically changing the default DB? I think that would be a big no-no! There is no way for SQL Server to know what the business rules are and it should therfore not change a default DB when/if the DB is unavailable. We as DBA's just need to make sure we have an sa account that always has master as the default db.

On my systems all logins are trusted connections with the exception of the sa account and another account I use for the linked servers we have. For these accounts my boss and I are the only ones who know the passwords. The sa account is ONLY used when absolutely needed. Otherwise all work on the DB is done through my trusted account.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Hendra Eka Putra SSCrazy Points: 2015 More actions · Answer 3

And also never forget to backup your master database each time you do some transactions that change yor user login information.

noeld SSC Guru Points: 96590 More actions · Answer 4

Well I NEVER set the default to master. I point them all to tempdb Instead

* Noel

Randy_Dyess SSC Eights! Points: 974 More actions · Answer 5

I make it a habit of not allowing any login to have master as the default DB. I even go as far as creating an empty DB that I point everyone too. No real reasoning behind this right now, just a feeling that someone will be able to find an exploit before I do and I do not want them to gain too much of a foothold in my installation. Same reasoning applies to dropping Pubs and Northwind from production servers.

As for your problem, you did exactly what you should have done. Logged in with another admin account and take care of the problem. Trying to be flexible and allowing users to change default settings after they are locked out probably entail creating a admin account and letting people use that (not recommended).

You should not be using SA to admin your installation. Create another login with sysadmin priviledges and use that to admin your installation. The SA account should have a very strong password assigned to it and then locked behind glass with a note saying "Only break glass in extreme emergencies". This practice would solve your fears of not having another admin account.

Just my two cents

Randy

http://www.Database-Security.Info

fhanlon SSChampion Points: 13131 More actions · Answer 6

I don't think you can prevent access to master. Everyone has access to master, whether its the default db or not via the guest account - which can't be dropped.

Francis

Randy_Dyess SSC Eights! Points: 974 More actions · Answer 7

Wasn't my intention to "prevent" access to master. You are correct in that everyone has access to master because the guest account is active in master. My intention was to "limit" access and damage. I treat system database and objects as off-limits to users as much as possible.

If there is not a system reason, master access is a system reason for login purposes as is EXECUTE permissions on some of the extended stored procedures, I try not to let users access these objects. Dropping sample database, pointing users to other default databases, dropping EXECUTE permissions on a select group of extended stored procs are just some of the examples of trying to stop problems before they happen.

Jon McCoy SSC Rookie Points: 28 More actions · Answer 8

I just "inherited" a MS-SQL server, which has been mismanaged. All the accounts (including sa) have a user database set as their default database -- which a user (logged in as sa) detached yesterday to copy to their dev server. Now I need to re-attach the database, but since it's not available, no login works.

I have a recent backup of master and the user database, but no way to connect to the server to restore them, or attach the user database. Any advice?

-jon-

SQLBill SSC Guru Points: 51440 More actions · Answer 9

At this point you might have to reinstall SQL Server and then attach the old MASTER and user databases. You might get away with only attaching the user database after the reinstall - that's what I would try first.

-SQLBill

Jon McCoy SSC Rookie Points: 28 More actions · Answer 10

Thankfully, I got an answer via email that worked a lot better than re-installing SQL server. Too bad I can't send a bottle of scotch vie email, I think I owe them one.

"The trick is to use the SIMPLE sql interface: ISQL.exe

ISQLW & Enterprise Mangler are higher level "helpful" tools that get all fscked up when something ugly happens.

isql -S[server] -Usa -dmaster -P[sa pwd] -Q"exec sp_defaultdb 'sa','master'"

-Q = "query to run then immediately exit"

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 11

Here is the most simplest way, set all your user to use pubs as their default database. You could change this by using:

sp_defaultdb [ @loginame = ] 'login' ,

[ @defdb = ] 'database'

Once you change their defaultdb, they should be able to login as usual. I am assuming that you have the admin right or a member of a group that has sa priveleges.

mom