0

This is relatively similar to questions such as these:

How can I change my default database in SQL Server without using MS SQL Server Management Studio?

https://superuser.com/questions/364825/sql-server-management-studio-ignores-default-db

That being said, Management Studio is ignoring all the suggestions. I'm logging in as sa, and I can see that the default catalog for sa is being changed successfully, but Management Studio ignores these changes in the dropdown:

enter image description here

Even if I change it to specific_database_name, and even if I can look at the sa login Properties menu and see that it's set to specific_database_name, Management Studio will always default that combo box to master.

I've tried:

  1. Exec sp_defaultdb @loginame='sa', @defdb='specific_database_name'
  2. ALTER LOGIN sa WITH DEFAULT_DATABASE = specific_database_name
  3. Going into the Properties menu for login sa in Management Studio and setting it in the dropdown box there.

The OP in the second question eventually fell back to using a batch file to log in as a different user, but I'd personally just rather keep having master show up. Also he did mention being able to set this on the connection properties themselves, but it's greyed out on my system, and I seem to remember being able to set this for an individual user a long time ago on another machine.

How can this be set? Note that this is not using a Windows login, but a SQL Server one instead. Thanks.

Community
  • 1
  • 1
Panzercrisis
  • 4,590
  • 6
  • 46
  • 85
  • If it's a windows login, then it's probably inheriting it from the group. Besides, it's working as expected for me, and always did. – dean Mar 25 '15 at 13:50
  • It works fine for me. How exactly are you creating the database window? Give us explicit steps so the we can reproduce this. – RBarryYoung Mar 25 '15 at 18:04
  • @RBarryYoung I open Management Studio, and I enter sa's password in the credentials pop-up. Everything else is pre-populated. Then I just click Connect, then New Query. – Panzercrisis Mar 25 '15 at 18:21

1 Answers1

0

I don't know why these methods are not working. Just to be sure I just did the following (on SQL Server 2012 enterprise)

1) Created Login test and assigned x1 as default database
2) Added login test to database x1 (and made test a member of datareaders)
3) Reopened SSMS - Logged in as Test
4) Opened query window - was placed in x1.

You really want to make users default to a database that is not a system database - otherwise they will attempt to create objects in Master which is something you really want to avoid.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22