Object & Statement Permissions
See Also: Main_Page - Database Administration - Security
Even after a user has been granted the privilege to connect to a particular database and / or be a member of a particular database role they might still not have any permissions to perform any action. What a user can do after connecting to the database is determined by statement and object permissions of a database user or roles that she belongs to.
Statement and object permissions can be granted to database users, user-defined database roles, Windows users or groups. You cannot grant permissions to SQL Server logins. To grant any level of administrative privileges you must make the login part of one of the built-in server role.
Object permissions allow a user to read and write data from tables and views, execute stored procedures and user-defined functions. Statement permissions allow a user to create, modify or drop databases and database objects, and take database and transaction log backups. Members of the SYSADMIN role and database owners can assign the following statement permissions:
Object permissions allow users to read and write data from tables and views and execute stored procedures and user-defined functions. Database owners, database object owners and database security administrators can grant permissions to:
User permissions in SQL Server 2000 are additive; this means that a user will have a combination of permissions granted to all Windows groups or SQL Server roles that this user belongs to. For example, if JohnDoe is a member of the Finance group as well as the Accounting group he will have:
SELECT * FROM authors
Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'authors', database 'pubs', owner 'dbo'.
Therefore the most restrictive permission, DENY, takes precedence. When a user executes a particular statement SQL Server first checks for DENY on a statement or object permission that is required to satisfy the statement the user submitted. DENY is checked not only for the user's individual account but also for all Windows groups and database roles the user belongs to. If DENY is found, no further action is necessary - SQL Server will abort the statement and return an error. If DENY is not found, the evaluation process continues to find a GRANT permission. If the user account, any group, or role she belongs to has a GRANT permission on the object / statement then the statement is executed without further evaluation for permissions. If neither groups nor roles the user belongs to is granted the appropriate permission and the user account doesn't have the permission either then SQL Server aborts the statement and returns an error. The REVOKE statement simply de-activates previously issued GRANT or DENY.
Object and statement permissions are managed through GRANT, REVOKE and DENY commands.
You can obtain a report of all object and statement permissions for a particular user within the current database by executing the system procedure sp_helprotect. Executing this procedure without any parameters will generate a report of all permissions granted to all users. As you might guess such a report can be quite lengthy. It is better to limit the output of this routine by specifying one of the parameters. Click here for the full syntax.
The first parameter @name is the name of the statement permission (such as CREATE TABLE), or the database object on which you wish to check permissions. Database object names should include the object owner along with the object name. @username parameter can be used to limit the output of sp_helprotect to a particular security account, such as the database user or Windows group that has access to the current database. @grantorname is the security account that granted permissions. For instance, you can obtain all permissions granted by dbo user or JaneDeer. The final parameter @permissionarea specifies whether you wish to view object permissions, statement permissions or both. This parameter can have values of 's' for statement permissions, 'o' for object permissions or a combination of the two letters with or without spaces and commas. For example, to get all permissions for user JohnDoe within the pubs database granted by database owner we could execute the following:
sp_helprotect @username = 'johndoe', @grantorname = 'dbo', @permissionarea = 's, o'
Owner Object Grantee Grantor ProtectType Action Column dbo authors johndoe dbo Grant Delete . dbo authors johndoe dbo Grant Insert . dbo authors johndoe dbo Grant Select (All+New) dbo authors johndoe dbo Grant Update (All+New) . . johndoe dbo Grant Create Procedure . . . johndoe dbo Grant Create Table .
Notice that the report provided by sp_helprotect displays whether permission is granted, revoked or denied. Note also that for SELECT and UPDATE permissions a column list can be provided if the user is limited to view and modify only certain columns. If we were to allow reading of only au_lname, au_fname and contract columns for JohnDoe, his SELECT permissions for the authors table would be represented by three rows, as follows:
Owner Object Grantee Grantor ProtectType Action Column dbo authors johndoe dbo Grant Select au_lname dbo authors johndoe dbo Grant Select au_fname dbo authors johndoe dbo Grant Select contract
The expression "All+New" in the sp_helprotect output means that permission to read or modify a certain table applies to the existing columns, as well as any new columns that can be added in the future. The same column can have a value of "New" or "ALL", in which case the user is permitted to read or modify new columns or all columns in the table, respectively.
The following example lists users that have a permission to create tables in the pubs database:
sp_helprotect 'CREATE TABLE'
Owner Object Grantee Grantor ProtectType Action Column . . guest dbo Grant Create Table . . . johndoe dbo Grant Create Table . . . TableCreators dbo Grant Create Table .
The next example shows all users that are allowed to work with the authors' table:
Owner Object Grantee Grantor ProtectType Action Column dbo authors guest dbo Grant Delete . dbo authors guest dbo Grant Insert . dbo authors guest dbo Grant References (All+New) dbo authors guest dbo Grant Update (New) dbo authors guest dbo Grant Update au_id dbo authors guest dbo Grant Update phone dbo authors guest dbo Grant Update address dbo authors guest dbo Grant Update City dbo authors guest dbo Grant Update State dbo authors guest dbo Grant Update Zip dbo authors guest dbo Grant Update contract dbo authors johndoe dbo Grant Delete . dbo authors johndoe dbo Grant Insert . dbo authors johndoe dbo Grant Select au_lname dbo authors johndoe dbo Grant Select au_fname dbo authors johndoe dbo Grant Select contract dbo authors johndoe dbo Grant Update (All+New)