Microsoft® SQL Server® 2012 Bible

846

Part VI: Securing Your SQL Server

Granting Object Permissions with Code

Setting an object’s permission is the only security command that can be executed without a

system stored procedure being called:

GRANT [Permission] ON [Securable]

TO [User|Role] [WITH GRANT OPTION]

The permission options available for each securable are listed in Table 33-1.

TABLE 33-1 Permission Options by Securable

Securable Permission Options

Database BACKUP DATABASE

BACKUP LOG

CREATE DATABASE

CREATE DEFAULT

CREATE FUNCTION

CREATE PROCEDURE

CREATE RULE

CREATE TABLE

CREATE VIEW

Scalar Function EXECUTE

REFERENCES

Ta b l e

Table-Valued Function

View

SELECT

INSERT

DELETE

UPDATE

REFERENCES

Stored Procedure EXECUTE

The ALL permission option has been deprecated but is still available for backward

compatibility reasons.

The User or Role refers to the database username, any user-defi ned public role, or the public

role. For example, the following code grants SELECT permissions to Jose for the Person

.Person table of the AdventureWorks2012 database:

USE AdventureWorks2012;

GO

GRANT SELECT ON Person.Person TO [AgileBay\Jose];

GO

The next example grants UPDATE permissions to the public role for the SalesOrderHeader table:

USE AdventureWorks2012;

GO

c33.indd 846c33.indd 846 7/31/2012 10:01:24 AM7/31/2012 10:01:24 AM

http://www.it-ebooks.info