如何在不使用MS SQL Server Management Studio的情况下更改SQL Server中的默认数据库?

I dropped a database from SQL Server, however it turns out that my login was set to use the dropped database as its default. I can connect to SQL Server Management Studio by using the 'options' button in the connection dialog and selecting 'master' as the database to connect to. However, whenever I try to do anything in object explorer, it tries to connect using my default database and fails.

我从SQL Server上删除了一个数据库，但结果是我的登录被设置为使用drop数据库作为它的默认值。我可以使用连接对话框中的“options”按钮连接到SQL Server Management Studio，并选择“master”作为要连接的数据库。但是，每当我尝试在object explorer中执行任何操作时，它都会尝试使用我的默认数据库进行连接并失败。

Does anyone know how to set my default database without using object explorer?

有人知道如何在不使用对象资源管理器的情况下设置我的默认数据库吗?

8 个解决方案

#1

Alternative to sp_defaultdb (which will be removed in a future version of Microsoft SQL Server) could be ALTER LOGIN:

sp_defaultdb(在未来的Microsoft SQL Server版本中将被删除)的替代方法可以是更改登录:

ALTER LOGIN [my_user_name] WITH DEFAULT_DATABASE = [new_default_database]

#2

203

What you can do is set your default database using the sp_defaultdb system stored procedure. Log in as you have done and then click the New Query button. After that simply run the sp_defaultdb command as follows:

您可以使用sp_defaultdb系统存储过程设置默认数据库。如您所做的那样登录，然后单击新的查询按钮。然后运行sp_defaultdb命令如下:

Exec sp_defaultdb @loginame='login', @defdb='master'

#3

To do it the GUI way, you need to go edit your login. One of its properties is the default database used for that login. You can find the list of logins under the Logins node under the Security node. Then select your login and right-click and pick Properties. Change the default database and your life will be better!

要做到这一点，你需要编辑你的登录。它的一个属性是用于该登录的默认数据库。您可以在安全节点下的登录节点下找到登录列表。然后选择您的登录名并右键单击并选择属性。更改默认数据库，您的生活将变得更好!

Note that someone with sysadmin privs needs to be able to login to do this or to run the query from the previous post.

请注意，有sysadmin privs的人需要能够登录以完成此操作，或者运行前一篇文章中的查询。

#4

Thanks to this post, I found an easier answer:

多亏了这篇文章，我找到了一个更简单的答案:

Open Sql Server Management Studio

Open Sql Server Management Studio。
Go to object Explorer -> Security -> Logins

访问对象资源管理器->安全->登录。
Right click on the login and select properties

右键单击登录并选择properties
And in the properties window change the default database and click OK.

在属性窗口中更改默认数据库并单击OK。

#5

If you don't have permissions to change your default DB you could manually select a different DB at the top of your queries...

如果您没有权限更改您的默认数据库，您可以在查询的顶部手动选择不同的数据库。

USE [SomeOtherDb]
SELECT 'I am now using a different DB'

Will work as long as you have permission to the other DB

只要你有其他DB的许可就可以工作吗

#6

Click on Change Connection icon
单击“更改连接”图标
Click Options<<
点击选择< <
Select the db from Connect to database drop down
从Connect到database下拉菜单中选择db

#7

In case you can't login to SQL Server:

如果您无法登录到SQL Server:

sqlcmd –E -S InstanceName –d master

Reference: https://support.microsoft.com/en-us/kb/307864

参考:https://support.microsoft.com/en-us/kb/307864

#8

-1

If you use windows authentication, and you don't know a password to login as a user via username and password, you can do this: on the login-screen on SSMS click options at the bottom right, then go to the connection properties tab. Then you can type in manually the name of another database you have access to, over where it says , which will let you connect. Then follow the other advice for changing your default database

如果您使用windows身份验证，并且您不知道通过用户名和密码登录用户的密码，您可以这样做:在SSMS的登录屏幕上，单击右下方的选项，然后转到connection properties选项卡。然后，您可以手动输入您可以访问的另一个数据库的名称，在它所指定的位置，这样您就可以进行连接。然后按照其他建议更改默认数据库

https://gyazo.com/c3d04c600311c08cb685bb668b569a67

#1