Managing SQL Server Permissions

Whether you are managing statement or object permissions, in Enterprise Manager or with T-SQL, all permission-management revolves around three commands: GRANT , REVOKE , and DENY . When a permission is granted, the user or role is given the permission to perform an action, such as creating a table. The sysprotects table keeps track of permissions, so when a GRANT command is issued, a row is added to sysprotects authorizing the action. The REVOKE command is the opposite ; it deletes the associated line from sysprotects. This has the effect of removing the ability to perform the action. These permissions are cumulative, meaning that if Bob is in the sales role, and both Bob and the sales role have been granted delete permissions on the product table, Bob can delete rows from the table. If I then revoked the delete permission from Bob, he could still delete from the table, as he is in the sales role. This is where DENY fits in. If Bob must remain in the sales role, but you want to stop him from deleting records, you can DENY Bob the delete permission. Rather than deleting the row from sysprotects as the REVOKE command does, DENY changes the entry to disallow the action. If the user or any group or role with whom he is associated has a DENY entry for an action, that action is not allowed.

Managing SQL Server Statement Permissions

Statement permissions control the ability to manage objects in SQL Server. Generally, statement permissions are managed by adding users to fixed server and database roles. They can, however, also be managed on a statement-by-statement basis. An example of this might be to grant a developer the ability to create tables and views.

Using Enterprise Manager

Statement permissions are managed through the Permissions tab of the Database Properties page. A green check indicates a permission is granted, a blank box indicates the permission has not been granted or has been revoked (no entry exists in sysprotects), and a red X indicates that the permission is denied . The Permissions tab is shown in Figure 15.9.

Figure 15.9. The Database Properties Permissions tab.

Using T-SQL

The syntax for the three T-SQL statement Permissions commands is as follows :

 GRANT { ALL  statement [ ,...n ] }  TO {  userrole}  [ ,...n ] DENY { ALL  statement  [  ,...   n  ] } TO {  userrole}  [  ,...   n  ] REVOKE { ALL  statement  [  ,...   n  ] } FROM {  userrole}  [  ,...   n  ] 

If ALL is specified, then all statement permissions are granted; otherwise , a single statement or comma-separated list can be specified. Likewise, the user or role can be single or multiple entries. The following are some examples of managing statement permissions:

 GRANT ALL to HR_Role  GRANT CREATE TABLE to DEV_Role REVOKE CREATE TABLE FROM HR_Role DENY CREATE VIEW to bobw 

Managing SQL Server Object Permissions

Object permissions or DML commands manage access to data. These are the permissions granted to users and roles to allow queries and changes in the database.

Using Enterprise Manager

Granting object permissions in Enterprise Manager can be done in one of two ways, depending on whether you want to approach permissions from a user or object perspective. From the user point of view, access the Properties page of the user or role and select Permissions. This brings up the Permissions tab, from which you can view the possible permissions on all objects, or select to view just the permissions assigned to the user or role. It is also possible to manage column-level permissions from here. Figure 15.10 shows the Permissions tab for a role.

Figure 15.10. The Permission tab for a role.

The other approach to object permissions is from the object. From the Properties page of the object on which you want to manage permissions, select the Permissions button. This brings up the Permissions tab showing all permissions assigned to the object. Figure 15.11 shows the Permissions tab for the Categories table. Note that even though public (of which Paul is a member) has been granted delete permission, Paul will not be able to delete, as he has been denied.

Figure 15.11. The Database Properties Permissions tab.

Using T-SQL

Many administrators find it easier to manage object permissions through T-SQL statements. The main advantage to this method is that if many changes need to be made, the changes can be scripted. Object permissions use an expanded version of the same syntax used to manage statement permissions. The following are some examples of statements used to manage object permissions.

To give Bill the ability to select, insert, and update data in the emp table, use the following:

 GRANT SELECT, INSERT, UPDATE on EMP to bill 

Following is the same statement; however, Bill can now grant the permissions to other users.

 GRANT SELECT, INSERT, UPDATE on emp to bill  WITH GRANT OPTION 

To DENY Bill the UPDATE permission, use the following:

 DENY UPDATE on emp to bill 

To remove a DENY from Bill (note that REVOKE...to is used as opposed to REVOKE...from ), use the following:

 REVOKE UPDATE on emp to bill