August 15, 2008 at 3:51 am
Hey nerds.
I had a pretty interesting morning.
Our support team informed us that they wanted to do some major server maintenance this coming weekend. I decided that to make doubly sure that we have our database backed up, I would make 2 separate copies of the database to 2 different network locations, independent of each other. I decided to detach the db and copy the files to these network locations. (I don’t have enough space on the server for a regular backup.)
Without me knowing, the default login for the "sa" user (and all other logins as well ) was the database I intended to detach, which I so did.
Here’s where the "fun" begins.
When I wanted to connect to the server using "sa" to reattach the db after the backup, the default db isn’t there anymore (since that’s the exact one I want to reattach.) Thankfully after little searching on the net I found the whole Method:
""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -S SQLSERVER2005 -d master -E
And
sp_defaultdb 'SQLSERVER2005\dbuser', master"
NOW !!
I ran it for sa, but alas, no luck. Even though the default db is now Master for login sa, still no go.
Fortunately I had a second user (Lets call it IAmShmuck) with admin rights that uses windows authentication, I did the method explained above, and HEY!!!, It worked.
First thing I did (while logged in as IAmShmuck) was go and change "sa" default login db to master. I tested sa, but NOPE still not.
Using IAmShmuck login I reattached my precious little database, logged out, then tried to log in using sa, AND IN SHE GOES !!!. no complaints no issues whatsoever. While logged in as sa I checked the default db login and it states clearly that its master. If I take my database offline and login using sa, again, its stops me, cant find the default db.
Seems to me that even if a change the default db to whatever my heart desires, the login still looks for the original.
Is this a bug, or should I retain my IAmShmuck login for life?
August 15, 2008 at 5:19 am
Setting the Default database to anything other than master is asking for trouble as you discovered. The root cause is that within the logins system table, the default database is stored as the dbid and the default database name is not defined.
Assume that there is a castastrophic hardware failure and all databases needed be recovered from backup where the user databases are named Customers and Products. Originally, database Customer has dbid of 6 and Products has a dbid of 7 and there was no user databases with a dbid of 5.
When the restore is performed, the Customers database is restored first and is assigned dbid of 4 and then the Products database is restored and is assigned a dbid of 5.
Now none of the default database configurations work!
Recommend that the default database always be master and the connection settings should specify a change in database context.
If this is not possible, insure that all DBA logins have a default database of master.
SQL = Scarcely Qualifies as a Language
August 15, 2008 at 5:24 am
Sorry, a correction:
Customers database is restored first and is assigned dbid of 5 and then the Products database is restored and is assigned a dbid of 6.
The dbids of 1 thru 4 are fixed and assigned to:
DBID Database Name
1master
2tempdb
3model
4msdb
SQL = Scarcely Qualifies as a Language
August 15, 2008 at 5:35 am
So what you saying is, just dont make your default login db anything other than master. Its not a bug its just bad practice.
Thanx man.
August 15, 2008 at 7:37 am
"Its not a bug its just bad practice."
Default databases are a bad practice. About a year ago, I went thru a review with the Security and Auditing department on this very topic as they were proposing the standard that the default database could not be master and must be a user database. Note that access to the master database using the guest user is mandatory for all connections as the system stored procedure are used by ODBC and OLEDB. Removing the guest account from master, model or tempdb will cause fatal functionality errors.
Here is another scenario:
There are two user databases named Customers and Products.
A login has a access to both databases.
The login has a default database of Customers.
The Customers database is not available due to a scheduled activity such as a software upgrade.
On connect, the login will return a warning message.
If the application does not handle warning messages correctly, the application will not be usable.
create database defaultDB;
exec sp_defaultdb '{login}','DefaultDb';
drop database defaultDB;
To see the warning message (substitute {login} and {password} with the appropriate values.
C:\>isql.exe -S. -U{login} -P{password} -Q"select @@servername"
Msg 4062, Level 11, State 1, Server USADSMCIM7, Line 0
Cannot open user default database. Using master database instead.
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply