How to set user database to default database

Question

Post reply

How to set user database to default database

karthik M

One Orange Chip

Points: 29139
More actions
December 31, 2007 at 4:19 am

#182263

Experts,
If i open QueryAnalyzer, It shows the master database as the default database.
I want to change it to any user database. is it possible ?
Thanks in Advance.
karthik

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

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

Simon Liddle SSCrazy Points: 2580 More actions · Answer 1

You can set this using sp_defaultdb, or by opening the login's properties window in EM.

HTH,

Simon

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 2

In Enterprise Manager, expand the relevant server and get to the appropriate login under the Security folder. Right-click on the login and get the properties. Under the General tab select the database under Defaults.

For SQL Server 2005, it is pretty much the same in Management Studio; you have the Default Database option.

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Is there any restrction to use sp_defaultdb ? Because i have dbo permission only.

karthik

Simon Liddle SSCrazy Points: 2580 More actions · Answer 4

I can't remember the details - I think you can only change the default db for your own login unless you are sysadmin - it should tell you in BOL.

karthik M One Orange Chip Points: 29139 More actions · Answer 5

do you have any idea why sql server is showing master database as the default one ? why can't it shows Model or tempdb ? is there any reason ?

karthik

steve block SSCertifiable Points: 7698 More actions · Answer 6

The reason it shows master as the default database is because master is the default "default" database. When your login was set up, someone didn't take the time to change the default database for it and hence, master is its default database.

I can think of no good reason to set tempdb or model to the default database. What database is this user going to be using most? Assign that database as the default. If this person is just in training, Northwind or pubs could be used as the default.

Steve