使用SQL语句赋予SQL Server数据库登录用户权限

本文转载自：

作者Blog：http://blog.csdn.net/herman_chow/

 

GRANT

在安全系统中创建项目，使当前数据库中的用户得以处理当前数据库中的数据或执行特定的 Transact-SQL 语句。

语法

语句权限：

GRANT { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]

对象权限：

GRANT
    { ALL [ PRIVILEGES ] | permission [ ,...n ] }
    {
        [ ( column [ ,...n ] ) ] ON { table | view }
        | ON { table | view } [ ( column [ ,...n ] ) ]
        | ON { stored_procedure | extended_procedure }
        | ON { user_defined_function }
    }
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]

参数

ALL

表示授予所有可用的权限。对于语句权限，只有 sysadmin 角色成员可以使用 ALL。对于对象权限，sysadmin 和 db_owner 角色成员和数据库对象所有者都可以使用 ALL。

statement

是被授予权限的语句。语句列表可以包括：

• CREATE DATABASE
  
  
• CREATE DEFAULT
  
  
• CREATE FUNCTION
  
  
• CREATE PROCEDURE
  
  
• CREATE RULE
  
  
• CREATE TABLE
  
  
• CREATE VIEW
  
  
• BACKUP DATABASE
  
  
• BACKUP LOG

n

一个占位符，表示此项可在逗号分隔的列表中重复。

TO

指定安全帐户列表。

security_account

是权限将应用的安全帐户。安全帐户可以是：

• Microsoft® SQL Server™ 用户。
  
  
• SQL Server 角色。
  
  
• Microsoft Windows NT® 用户。
  
  
• Windows NT 组。

当权限被授予一个 SQL Server 用户或 Windows NT 用户帐户，指定的 security_account 是权限能影响到的唯一帐户。若权限被授予 SQL Server 角色或 Windows NT 组，权限可影响到当前数据库中该组或该角色成员的所有用户。若组或角色和它们的成员之间存在权限冲突，最严格的权限 (DENY) 优先起作用。security_account 必须在当前数据库中存在；不可将权限授予其它数据库中的用户、角色或组，除非已为该用户在当前数据库中创建或给予了访问权限。

两个特殊的安全帐户可用于 GRANT 语句。授予 public 角色的权限可应用于数据库中的所有用户。授予 guest 用户的权限可为所有在数据库中没有用户帐户的用户使用。

当授予某个 Windows NT 本地组或全局组权限时，请指定在其上定义该组的域名或计算机名，然后依次输入反斜线和组名。但是，若要授予访问 Windows NT 内置本地组的权限，请指定 BUILTIN 而不是域名或计算机名。

PRIVILEGES

是可以包含在符合 SQL-92 标准的语句中的可选关键字。

permission

是当前授予的对象权限。当在表、表值函数或视图上授予对象权限时，权限列表可以包括这些权限中的一个或多个：SELECT、INSERT、DELETE、REFENENCES 或 UPDATE。列列表可以与 SELECT 和 UPDATE 权限一起提供。如果列列表未与 SELECT 和 UPDATE 权限一起提供，那么该权限应用于表、视图或表值函数中的所有列。

在存储过程上授予的对象权限只可以包括 EXECUTE。在标量值函数上授予的对象权限可以包括 EXECUTE 和 REFERENCES。

为在 SELECT 语句中访问某个列，该列上需要有 SELECT 权限。为使用 UPDATE 语句更新某个列，该列上需要有 UPDATE 权限。

为创建引用某个表的 FOREIGN KEY 约束，该表上需要有 REFERENCES 权限。

为使用引用某个对象的 WITH SCHEMABINDING 子句创建 FUNCTION 或 VIEW，该对象上需要有 REFERENCES 权限。

column

是当前数据库中授予权限的列名。

table

是当前数据库中授予权限的表名。

view

是当前数据库中被授予权限的视图名。

stored_procedure

是当前数据库中授予权限的存储过程名。

extended_procedure

是当前数据库中授予权限的扩展存储过程名。

user_defined_function

是当前数据库中授予权限的用户定义函数名。

WITH GRANT OPTION

表示给予了 security_account 将指定的对象权限授予其它安全帐户的能力。WITH GRANT OPTION 子句仅对对象权限有效。

AS {group | role}

指当前数据库中有执行 GRANT 语句权力的安全帐户的可选名。当对象上的权限被授予一个组或角色时使用 AS，对象权限需要进一步授予不是组或角色的成员的用户。因为只有用户（而不是组或角色）可执行 GRANT 语句，组或角色的特定成员授予组或角色权力之下的对象的权限。

注释

不允许有跨数据库权限；只能将当前数据库中的对象和语句的权限授予当前数据库中的用户。如果用户需要另一个数据库中的对象的权限，请在该数据库中创建用户帐户，或者授权用户帐户访问该数据库以及当前数据库。

<!--NOTE-->

说明  系统存储过程是例外，因为 EXECUTE 权限已经授予 public 角色，允许任何人去执行。但是在执行系统存储过程后，将检查用户的角色成员资格。如果此用户不是运行此存储过程所需要的适当的固定服务器或数据库角色的成员，则此存储过程不会继续执行。

<!--/NOTE-->

 

REVOKE 语句可用于删除已授予的权限，DENY 语句可用于防止用户通过 GRANT 语句获得权限给他们的用户帐户。

授予权限删除所授予级别（用户、组或角色）上的已拒绝权限或已废除权限。在另一级别（诸如包含此用户的组或角色）上被拒绝的同一权限优先起作用。但是，虽然在另一级别上所废除的同一权限仍然适用，但它并不阻止用户访问该对象。

如果用户激活应用程序角色，对此用户通过该应用程序角色访问的任何对象，GRANT 的作用为空。因此，尽管一个用户可能被授予了对当前数据库中的指定对象的访问权限，但是如果此用户使用对此对象无访问权限的应用程序角色，则在应用程序角色激活期间，此用户也没有此对象的访问权限。

sp_helprotect 系统存储过程报告在数据库对象或用户上的权限。

权限

GRANT 权限依赖于所授予的语句权限和权限中涉及的对象。sysadmin 角色中的成员可在任何数据库中授予任何权限。对象所有者可为他们所拥有的对象授予权限。db_owner 或 db_securityadmin 角色的成员可授予其数据库中任何语句或对象上的任何权限。

需要权限的语句是那些在数据库中增加对象，或对数据库执行管理活动的语句。每条需要权限的语句都有一个特定的角色集，自动有权限执行此语句。例如，sysadmin、db_owner 和 db_ddladmin 角色的成员默认有CREATE TABLE 权限。sysadmin 和 db_owner 角色以及表的所有者默认有对表执行 SELECT 语句的权限。

有一些 Transact-SQL 语句不能被授予权限；执行这些语句要求有固定角色中的成员资格，此角色有默示执行特殊语句的权限。例如，若要执行 SHUTDOWN 语句，用户必须添加为 serveradmin 角色中的成员。

dbcreator、processadmin、securityadmin 和 serveradmin 固定服务器角色的成员仅有权执行以下 Transact-SQL 语句。

 

语句 dbcreator processadmin securityadmin serveradmin bulkadmin

ALTER DATABASE	X
CREATE DATABASE	X
BULK INSERT					X
DBCC				X (1)
DENY			X (2)
GRANT			X (2)
KILL		X
RECONFIGURE				X
RESTORE	X
REVOKE			X (2)
SHUTDOWN				X

(1) 有关更多信息，请参见 DBCC 语句。
(2) 仅适用于 CREATE DATABASE 语句。

<!--NOTE-->

说明  diskadmin 和 setupadmin 固定服务器角色的成员没有权限执行任何 Transact-SQL 语句，他们只能执行特定的系统存储过程。但是，sysadmin 固定服务器角色的成员有权限执行所有的 Transact-SQL 语句。

<!--/NOTE-->

 

下面的固定数据库角色的成员有权限执行指定的 Transact-SQL 语句。

 

语句
db_owner
db_datareader
db_datawriter
db_ddladmin db_backupoperator db_securityadmin

ALTER DATABASE	X			X
ALTER FUNCTION	X			X
ALTER PROCEDURE	X			X
ALTER TABLE	X (1)			X
ALTER TRIGGER	X			X
ALTER VIEW	X (1)			X
BACKUP	X				X
CHECKPOINT	X				X
CREATE DEFAULT	X			X
CREATE FUNCTION	X			X
CREATE INDEX	X (1)			X
CREATE PROCEDURE	X			X
CREATE RULE	X			X
CREATE TABLE	X			X
CREATE TRIGGER	X (1)			X
CREATE VIEW	X			X
DBCC	X				X (2)
DELETE	X (1)		X
DENY	X					X
DENY on object	X
DROP	X (1)			X
EXECUTE	X (1)
GRANT	X					X
GRANT on object	X (1)
INSERT	X (1)		X
READTEXT	X (1)	X
REFERENCES	X (1)			X
RESTORE	X
REVOKE	X					X
REVOKE on object	X (1)
SELECT	X (1)	X
SETUSER	X
TRUNCATE TABLE	X (1)			X
UPDATE	X (1)		X
UPDATE STATISTICS	X (1)
UPDATETEXT	X (1)		X
WRITETEXT	X (1)		X

(1) 权限也适用于对象所有者。
(2) 有关更多信息，请参见 DBCC 语句。

<!--NOTE-->

说明  db_accessadmin 固定数据库角色的成员没有执行任何 Transact-SQL 语句的权限，只可执行特定的系统存储过程。

<!--/NOTE-->

 

不需要权限即可执行的 Transact-SQL 语句有（已自动授予 public）:

 

BEGIN TRANSACTION	COMMIT TRANSACTION
PRINT	RAISERROR
ROLLBACK TRANSACTION	SAVE TRANSACTION
SET

有关执行系统存储过程所需权限的更多信息，请参见相应的系统存储过程。

示例

A. 授予语句权限

下面的示例给用户 Mary 和 John 以及 Windows NT 组 Corporate\BobJ 授予多个语句权限。

GRANT CREATE DATABASE, CREATE TABLE
TO Mary, John, [Corporate\BobJ]

B. 在权限层次中授予对象权限

下例显示权限的优先顺序。首先，给 public 角色授予 SELECT 权限。然后，将特定的权限授予用户 Mary、John 和 Tom。于是这些用户就有了对 authors 表的所有权限。

USE pubs
GO

GRANT SELECT
ON authors
TO public
GO 

GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
GO

C. 给 SQL Server 角色授予权限

下面的示例将 CREATE TABLE 权限授予 Accounting 角色的所有成员。

GRANT CREATE TABLE TO Accounting

D. 用 AS 选项授予权限

用户 Jean 拥有表 Plan_Data。Jean 将表 Plan_Data 的 SELECT 权限授予 Accounting 角色（指定 WITH GRANT OPTION 子句）。用户 Jill 是 Accounting 的成员，他要将表 Plan_Data 上的 SELECT 权限授予用户 Jack，Jack 不是 Accounting 的成员。

因为对表 Plan_Data 用 GRANT 语句授予其他用户 SELECT 权限的权限是授予 Accounting 角色而不是显式地授予 Jill，不能因为已授予 Accounting 角色中成员该权限，而使 Jill 能够授予表的权限。Jill 必须用 AS 子句来获得 Accounting 角色的授予权限。

/* User Jean */
GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION

/* User Jill */
GRANT SELECT ON Plan_Data TO Jack AS Accounting

<!--RELATEDTOPICSLIST-->