You can manage user access to your database by using several techniques. In the past few T-SQL Admin columns, I've discussed how you can use usernames and roles to control database access for individuals and groups. You control user access to specific data by using permissions. Permissions let you control exactly which users have access to what information.
You always grant SQL Server permissions to users within a database, not to login names. For working with permissions, SQL Server 2000 and 7.0 provide three commands that are similar to the commands you use to grant permissions at the Windows OS level: GRANT, DENY, and REVOKE. System tables keep track of permissions information, and SQL Server offers several ways you can extract information about existing permissions.
Permissions Commands
SQL Server has two types of permissions, both of which use the same basic commands. Statement permissions allow users to perform actions such as backing up a database or creating a table, a view, or a procedure. For users in the master database only, you can grant permissions to create a new database. Only users in the db_owner or db_securityadmin roles can control who has statement permissions. Note that the username dbo is always in the db_owner role, so dbo can always control permissions; users in the server role sysadmin automatically have the username dbo in every database, so they can also control permissions.
Object permissions (the second type of permissions) control data access. Users in the db_owner or db_securityadmin roles and object owners can use object permissions to control access to objects. Owners of tables or views can determine who can insert, update, delete, or select from their objects and who can use their objects as the referenced object for a foreign key. Owners of stored procedures or scalar functions can determine who has permission to execute their routines.
When you use T-SQL syntax, the statements for controlling statement permissions and object permissions look similar. The only real differences between statement and object permissions are that object permissions need an object name, and you can include a list of columns for SELECT or UPDATE permissions when you specify object permissions.
As I mentioned in "Managing Multiple-User Access," October 2002, you can use the commands GRANT, DENY, and REVOKE when you're working with permissions for the guest username. These are the same commands you use for granting permissions to anybody in the database. GRANT lets a user, OS group, or role execute a statement or access data. When granting object permissions, you can also use the WITH GRANT OPTION clause, which lets users who have permissions grant those permissions to others. DENY prevents a user, OS group, or role from executing a statement or accessing data. REVOKE removes a previously granted or denied permission.
In SQL Server 6.5 and earlier releases, the DENY command isn't available, so the REVOKE command serves double duty. REVOKE can remove a previously granted permission or it can prevent a particular user from performing an action. Numerous bugs and ambiguities have occurred because of this double meaning.
As it does in Windows OS permission control, a DENY that's applied to any role or group that a user belongs to means that the user has no permissions. For example, if user sue is a member of the clerks role and you execute the following two statements (in any order), sue won't be able to select from the inventory table:
GRANT SELECT ON inventory TO sue DENY SELECT ON inventory TO clerks
Denying permissions to the clerks role overrides sue's individual user permissions. However, you usually grant permissions at a high level (e.g., a group or a role) and deny permissions at a lower level (e.g., an individual user):
GRANT SELECT ON inventory TO clerks DENY SELECT ON inventory TO sue
These two statements let everybody in the clerks role except sue select from the inventory table.
You should be particularly careful about denying permissions to the public role, because public includes everybody except members of the sysadmin server role, for whom SQL Server never checks permissions. If a non-sysadmin object owner denies access to the public role, she also denies herself access to her own objects. Because these relationships can be complex, you need to keep track of which users have which permissions. The system tables contain this information.
System Tables
The two most important tables you use when you manage permissions are the sysusers and syspermissions tables. However, if you read the code for most of the system stored procedures that deal with permissions (e.g., sp_helprotect), you'll see that the code contains few references to the syspermissions table; instead, the code references the sysprotects virtual table. In SQL Server 6.5 and earlier, the sysprotects system table maintained all permissions. In SQL Server 7.0, Microsoft introduced the syspermissions table, which exists in every database to track permissions that are granted or denied to users. Syspermissions also stores object permissions. The sysprotects table still exists for backward compatibility. In SQL Server 7.0 and later, sysprotects is a pseudo table, which appears as a normal table in the system but SQL Server generates its contents only when you access it. Thus, a pseudo table takes up no storage space. However, SQL Server treats a pseudo table just like it treats a real table in all code and system stored procedures. (The sysfiles system table is an example of a pseudo table. For a description of how sysfiles works, see "Roll Your Own System Stored Procedures," November 2001.) Most of the system stored procedures in SQL Server 7.0 and later still contain references to sysprotects, and the information available through sysprotects is much easier to work with.
The code in Listing 1 uses the sysprotects table, which contains mostly numerical information. The first two columns are numerical foreign keys. ID is the object ID of the object to which the permissions apply; for statement permissions, ID is 0. The UID column is the user ID of the user to whom the permissions apply. The next two columns, action and protecttype, are coded values; you can find out what they mean by looking at the spt_values table. Open the procsyst.sql file, which contains the code for all system procedures, and search for this table name. Spt_values is a big lookup table that lets you translate the coded values in the action and protecttype columns in sysprotects into English words. The value in the protecttype column records whether a particular permission is granted or denied, and the value in the action column shows which permission (e.g., SELECT, CREATE TABLE) is being controlled. (For more information about the spt_values table, see "A Bit About Bits," December 2001.) SQL Server Books Online (BOL) explains what each action and protecttype value in the sysprotects table means, but the description includes an error. The documentation says that a protecttype value of 206 means REVOKE, but this can't be true. REVOKE only removes rows that are already in sysprotects—it never creates new rows referring to that REVOKE action. Protecttype 206 actually means DENY.
The procedure sp_permissions_granted_to_guest, which Listing 1 shows, looks at rows in spt_values that have a type value of T because T indicates rows pertaining to permissions. The sp_permissions_granted_to_guest procedure looks for a protecttype of 204 or 205 for GRANT or GRANT WITH GRANT OPTION because the procedure was designed to report only about granted permissions, not denied permissions. You can rewrite the procedure if you want it to do something different such as look for denied or revoked permissions. In the sysprotects table, the columns column is for those SELECT and UPDATE permissions that apply to only a subset of columns in a table.
Could you use a procedure similar to sp_permissions_granted_to_guest to determine what permissions any user has? Yes; the documented procedure you can use for that purpose is sp_helprotect. You can also use the information schema views TABLE_PRIVILEGES and COLUMN_PRIVILEGES to get information about object permissions. (No information schema view reports about statement permissions.) However, sp_helprotect, the information schema views, and the Enterprise Manager GUI report only about permissions directly granted or denied to a particular user—they don't report about inherited permissions. These tools look only for rows in sysprotects that contain the requested UID. But what if you want to know a user's effective permissions? Effective permissions are the permissions that a user can actually use, whether they were granted, revoked, or denied specifically to the user or to a group that the user belongs to. For example, suppose you've granted permission to several different groups or roles in which the user MyDomain\sam is a member. How do you find out whether MyDomain\sam can perform a given action?
SQL Server determines effective permissions internally, but it would be nice if administrators could find out what those permissions are during testing so they could be sure no users had accidentally been given permissions to do things they shouldn't do. The only supplied tool that helps with this task is a supplied function called permissions(), to which you can pass an optional object ID and an optional column name. The function returns an integer, which is a bitmap representing the permissions for the current user. If you don't supply an object name, the bits returned in the bitmap represent effective statement permissions—each bit represents one of the grantable permissions. If the bit representing a particular statement permission is 1, the current user has that statement permission, and if the bit is 0, the current user doesn't have that permission. If you supply an object name, the bits represent the possible object permissions. For full details about what each bit means, see the BOL documentation for the permissions() function.
I've written the first version of a stored procedure called sp_testpermissions, which lets me determine the effective permissions for any user. This procedure, which Listing 2 shows, requires that you pass it three parameters: a username, a table or view name, and an action (i.e., SELECT, INSERT, UPDATE, DELETE, or REFERENCES). The procedure responds with a message that says the specified user can perform the given action on the object or that the user can't perform the action.
This first version of the sp_testpermissions procedure has some limitations, but the BOL documentation for permissions() provides information that can help you extend the procedure for your own use. For example, this procedure only works for tables and views, not for functions or procedures for which you can grant EXECUTE permissions. And the procedure doesn't report statement permissions or object permissions for individual columns.
The sp_testpermissions procedure works for users other than the current user by taking advantage of the command SETUSER, which is included in SQL Server 2000 and 7.0 for backward compatibility only. SETUSER followed by a valid database username changes the effective username to the username you supply, and SQL Server performs all subsequent permission testing for the newly assumed username instead of the original username. The new username stays in effect until SETUSER is run without any username. Beware that if SETUSER executes in a stored procedure, the end of the procedure doesn't cause the new username to revert to the original username, so my procedure includes a SETUSER with a username before it calls the permissions() function, and a SETUSER without a username after it calls the function. I can only hope that when Microsoft does away with SETUSER, the company will provide another way to verify other users' permissions. Also, the documentation says that SETUSER doesn't work for Windows users—only for SQL Server users—but I haven't found this to be the case. The only limitation I've found is that you can't use SETUSER to determine the permissions granted to an OS group, but setting the username to a group name wouldn't make much sense anyway because a username usually applies to only one person.
In addition to making sp_testpermissions more robust, you could include it in a larger procedure that returns all the effective permissions for a given user, or for a given object, or both, but I'll leave that as an exercise for the reader.
Managing permissions and database access can be a complex job, and sometimes you can overlook important details. In the next T-SQL Admin column, I'll look at details you should remember about the permissions you need on dependent objects—such as the tables or views that a stored procedure accesses—and cross-database permissions.
