设置mysql的schema_sql-server – 如何将用户DEFAULT_SCHEMA设置为“...

最新推荐文章于 2024-07-30 03:54:58 发布

上官义飞

最新推荐文章于 2024-07-30 03:54:58 发布

阅读量1.9k

点赞数

文章标签：设置mysql的schema

本文链接：https://blog.csdn.net/weixin_36257554/article/details/114046779

版权

本文介绍了SQL Server中两种情况下的数据库访问：一种是登录拥有CONTROLSERVER权限，能直接创建对象；另一种是通过Windows组成员身份访问。当没有数据库用户账户的用户创建对象时，系统会自动为其创建数据库主体和默认模式。示例展示了如何在不指定现有模式的情况下创建对象，并展示了相关查询以验证结果。

摘要由CSDN通过智能技术生成

发生这种情况的一种方法是使具有CONTROL SERVER服务器级权限的登录(因此不需要实际的USER以访问数据库),而不是sysadmin固定服务器角色(因为这是隐含的默认模式) (dbo)创建一个对象而不显式指定要放入的模式.

Implicit Schema and User Creation

In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

A login has CONTROL SERVER privileges.

A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

因此,我说的是上面提到的第一个案例,@ Max的回答是关于第二个案例.

以下是第一种情况的示例(请注意,以下示例使用tempdb,因为每个实例都有,但我确实在非tempdb数据库上进行了测试以确保).要查看第二种情况的示例,请参阅@ Max的answer(此处无需复制).

USE [master];

CREATE LOGIN [DefaultSchemaTest] WITH PASSWORD = 'DefaultSchemaTest';

GRANT CONTROL SERVER TO [DefaultSchemaTest];

EXECUTE AS LOGIN = 'DefaultSchemaTest';

USE [tempdb];

SELECT * FROM sys.database_principals WHERE [type] <> 'R';

SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

CREATE TABLE MySchemaTest (Col1 INT);

SELECT * FROM sys.database_principals WHERE [type] <> 'R';

SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';