sql设为简单模式sql
This article gives an overview of SQL Schema and its usage in SQL Server.
本文概述了SQL架构及其在SQL Server中的用法。
模式简介 (Introduction to Schema)
A SQL database contains multiple objects such as tables, views, stored procedures, functions, indexes, triggers. We define SQL Schema as a logical collection of database objects. A user owns that owns the schema is known as schema owner. It is a useful mechanism to segregate database objects for different applications, access rights, managing security administration of databases. We do not have any restrictions on the number of objects in a schema.
SQL数据库包含多个对象,例如表,视图,存储过程,函数,索引,触发器。 我们将SQL Schema定义为数据库对象的逻辑集合。 用户拥有的拥有架构的被称为架构所有者。 这是一种有用的机制,可以为不同的应用程序,访问权限,管理数据库的安全性管理分离数据库对象。 我们对模式中的对象数量没有任何限制。
Starting from SQL Server 2005, we have different meanings of user and schema. Now, database objects owner is a schema, and we define schema owners. We can have a single or multiple schema owners. It provides the following benefits:
从SQL Server 2005开始,我们对用户和架构的含义有所不同。 现在,数据库对象所有者是一个架构,我们定义了架构所有者。 我们可以有一个或多个架构所有者。 它具有以下优点:
- We can quickly transfer ownership of a SQL schema to another user 我们可以快速将SQL模式的所有权转让给其他用户
- We can share a schema among multiple users 我们可以在多个用户之间共享模式
- It allows you to move database objects among the schemas 它允许您在模式之间移动数据库对象
- We get more control over database objects access and security 我们可以更好地控制数据库对象的访问和安全性
We define default SQL Schema for a database user in the create database user window:
我们在创建数据库用户窗口中为数据库用户定义默认SQL模式:
If we do not define any default schema for a user, SQL Server assumes dbo as the default schema. We can verify the default schema for a user using the following system function:
如果我们没有为用户定义任何默认架构,则SQL Server会将dbo假定为默认架构。 我们可以使用以下系统功能来验证用户的默认架构:
SELECT SCHEMA_NAME();
检索数据库中的所有架构及其所有者 (Retrieve all schema and their owners in a database)
We can query sys.schemas system table to find out schema in a database and their owners:
我们可以查询sys.schemas系统表来找出数据库及其所有者的架构:
SELECT s.name AS schema_name,
s.schema_id,
u.name AS schema_owner
FROM sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name;
In the following screenshot, we can see master database schema and their owners:
在以下屏幕截图中,我们可以看到主数据库架构及其所有者: