Microsoft® SQL Server® 2012 Bible

847

Chapter 33: Authorizing Securables

33

GRANT UPDATE ON Sales.SalesHeader TO Data_Analyst;

GO

Multiple users or roles, and multiple permissions, may be listed in the command. The fol-

lowing code grants select and update permission to the Developer user and to the DBATeam

Windows Group:

GRANT SELECT, UPDATE ON Sales.SalesHeader to Developer, Data_Analyst;

The WITH GRANT option provides the ability to grant permissions to others for the object

specifi ed. For example, the following command grants Jose the permission to select from

the SalesHeader table and grants select permission to others:

GRANT SELECT ON Sales.SalesHeader TO [AgileBay\Jose] WITH GRANT OPTION;

Revoking and Denying Object Permission with Code

Revoking and denying object permissions uses essentially the same syntax as granting per-

mission. The following statement revokes select permissions from Jose on the Sales

.SalesHeader table:

REVOKE All ON Sales.SalesHeader TO [AgileBay\Jose];

If the permission included the WITH GRANT option, then the permission must be revoked

or denied with the CASCADE option so that the WITH GRANT OPTION will be removed. The

following command denies select permissions from Jose on the Person table:

DENY SELECT ON Person.Person TO [AgileBay\Jose]CASCADE

Because using CASCADE can revoke not only the WITH GRANT OPTION permission, the

DBA can also get rid of the ability to GRANT but must fi rst get rid of the permission

including WITH GRANT OPTION and then re-grant the original permission, but this time

without specifying the WITH GRANT OPTION.

The Public Role

The public role is a fi xed role, but it can have object permissions like a user-defi ned role.

Every user is automatically a member of the public role and cannot be removed, so the pub-

lic role serves as a baseline or minimum permission level.

Be careful when applying permissions to the public role because it affects everyone except members of the

sysadmin role. Denying access to the public role blocks all users, even object owners, from accessing the data.

Most importantly, remember that granting access to the public role allows all users to access the data. The excep-

tion is any login that maps to dbo and members of the sysadmin fi xed server role because they bypass all security

permissions.

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

http://www.it-ebooks.info