更新时间:2022-10-14 20:07:37
在第二个语句中,您明确定义了您为其授予权限的类(数据库)。考虑一个示例,其中您在名为Test的数据库中具有名为Test的对象。如果没有定义类,可能不清楚你要授予哪个类权限。
通过查看无效GRANT语句的错误消息,可能最容易理解
以下声明GRANT ALTER ON NotExisting TO SomeOne
会产生消息15151,等级16,状态1,行4
找不到对象'NotExisting' ,因为它不存在或你没有权限。
而GRANT ALTER ON database :: NotExisting TO SomeOne
将输出消息15151,等级16,状态1,第6行
可以找不到数据库'NotExisting',因为它不存在或你没有权限。
在你的第一个例子中你没有定义任何安全,因此默认情况下使用数据库。
两者都执行相同的操作GRANT ALTER TO
USER 给予特定用户的所有对象的alter权限GRANT ALTER ON
用于授予特定架构的权限,请参阅 GRANT架构权限(Transact-SQL) [ ^ ]
Hi all.
I'm using SQL Server 2014.
I've written 2 codes for grant permission.
Please see codes below.
what's difference between them?
regards.
What I have tried:
1)
use testDB
go
grant alter to username
2)
use testDB
go
grant alter on database::testDB to username
In the second statement you explicitly define the class (database) for which you grant privileges. Consider an example where you have and object called Test in a database named Test. Without defining the class it could be unclear for which class you are about to give permissions.
It's perhaps easiest to understand by looking at the error messages for invalid GRANT statement
The following statementGRANT ALTER ON NotExisting TO SomeOne
would produceMsg 15151, Level 16, State 1, Line 4 Cannot find the object 'NotExisting', because it does not exist or you do not have permission.
whileGRANT ALTER ON database::NotExisting TO SomeOne
would outputMsg 15151, Level 16, State 1, Line 6 Cannot find the database 'NotExisting', because it does not exist or you do not have permission.
In your first example you don't define any securable so database is used by default.
Both does the same operationGRANT ALTER TO
USER is giving alter permission on all objects to the particular userGRANT ALTER ON
used to give permission on particular schema , refer GRANT Schema Permissions (Transact-SQL)[^]