How can we find out if a particular database is the defualt database for any login ?

Question

How can we find out if a particular database is the defualt database for any login ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
February 7, 2012 at 12:40 am

#267952

Hi All,
I connected to one of the server and and detached one database.
After that I was not able to do anything is SQL Server as the detached database was my default database.
I couldn't even reattach it also.
My question is,
How can we find out if a particular database is the defualt database for any login ?
What are the things we need to check when we detach a database ?.
What are the things we need to check when we overwrite a database ?
Thanks in advance.
Smith.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Joy Smith San (2/7/2012)
Hi All,
I connected to one of the server and and detached one database.
After that I was not able to do anything is SQL Server as the detached database was my default database.
I couldn't even reattach it also.
My question is,
How can we find out if a particular database is the defualt database for any login ?
What are the things we need to check when we detach a database ?.
What are the things we need to check when we overwrite a database ?
Thanks in advance.
Smith.

the view sys.server_principals can give you the first part of who might have it as the default:

SELECT default_database_name,

COUNT(*) AS NumAsDefaultDB

FROM sys.server_principals

GROUP BY default_database_name

if dropped the database beforehand, you can simply alter the users to have master or some other database as their default

ALTER LOGIN [DOMAIN\login]

WITH DEFAULT_DATABASE = master

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks.. that was the problem.

The database dropped was the default DB of the logged in user.

And I dint know any other login password to set it.

Anyways, it was done finally.

Thanks.

Smith.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

As a sidebar, I'm loath to use the master DB as the default for any login because people forget they're there and end up saving tons of garbage in the master database. Except for the actual SA login, which is only used for critical emergencies, I force everyone's default to a "scratch" db.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 4

If anyone finds themselves in this situation (I know I have), there's a fairly easy way to still get into SSMS (assuming you have access to another database on the server).

In the "Connect to Server" dialogue, click on Options>>, go to the Connection Properties tab and type in the name of another database on the server (you have to type it rather than select from the dropdown, since it can't get a list of databases as your default database is wrong!).

If you're old-fashioned like me and like command lines, you can also use OSQL with the -d switch and then run sp_defaultdb from there.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

Thank you somuch...

I was not aware about it.. Thanks a lot.

Smith.

Stamey SSCertifiable Points: 5600 More actions · Answer 6

Jeff Moden (2/7/2012)
As a sidebar, I'm loath to use the master DB as the default for any login because people forget they're there and end up saving tons of garbage in the master database. Except for the actual SA login, which is only used for critical emergencies, I force everyone's default to a "scratch" db.

If I may continue on the sidebar for a moment, while I try to set all users to some other default DB than Master, I have found in my new position, that most accounts are not set up like that, but while the accounts have a default DB of Master, they cannot Connect, or do anything else there, so there is no problem of people saving unwanted objects in Master.

Are you speaking of SQL 2000 servers? There used to be an issue of the account needing access to their default DB otherwise they couldn't use SQL EM to connect. I don't think this applies to newer versions of SQL, and SSMS.

Thanks,

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.