Microsoft® SQL Server 2012 Unleashed

Using T-SQL to Manage Permissions

As you saw in the SSMS Permissions pages, three options exist for assigning every permission: GRANT, DENY, and REVOKE. Each option has its own T-SQL statements that can be used to manage permissions as well. The simplified syntax for the GRANT command is as follows:

Click here to view code image

GRANT { ALL [ PRIVILEGES ] }      | permission [ ( column [ ,...n ] ) ] [ ,...n ]      [ ON [ class :: ] securable ] TO principal [ ,...n ]      [ WITH GRANT OPTION ] [ AS principal ]

This basic GRANT syntax is similar to that in SQL Server 2000, but the addition of many permissions and securables in SQL Server 2005 and SQL Server 2012 has expanded the scope of the command. SQL Server 2005 also introduced the WITH GRANT ...