SQL Server Permissions

[Previous] [Next]

So, now you have a login to SQL Server. You've switched to a database context and established your identity within the database. What can you do? Well, not a whole lot—you need permissions to do everything within a database, unless you're a member of the sysadmin fixed server role. You can run three statements affecting persmissions: grant to give permissions to someone, deny to explicitly deny permissions, and revoke to remove a previous grant or deny. Two types of permissions exist: statement permissions, which give the rights to run particular statements, and object permissions, which give the rights to perform certain operations against a particular database object.

Permissions are cumulative, except for deny. A user can be associated with many types of permissions—those for themselves, those for each Windows group of which they are a member, and those for any SQL Server role of which they are a member. To find out a user's effective permissions, you need to add them all up. However, any deny is always preventative and always honored before any allow. So, if you have Select, Insert, Update, and Delete rights on a table because of group memberships but are individually denied (or any group of which you're a member is denied) Select on the table, you will be denied all access to the table.

Statement Permissions

Statement permissions give the database user the right to run a particular statement. There are no restrictions on how many times the user runs a particular statement. Upon running one of these statements—which are typically used to create objects—the user becomes the owner of the objects he or she created. The statement permissions that you can grant others the right to run are described in Table 6-2.

Table 6-2. SQL Server statement permissions.

Permission	Comments
create database	Allows a user to create databases. This permission is valid in the master database only, so you must create a user account for a login in master for the target user and then run the grant statement for that user in the master database.
create table	Allows a user to create tables. This permission is valid in every database.
create view	Allows a user to create views. This permission is valid in every database.
create procedure	Allows a user to create stored procedures. This permission is valid in every database.
create function (SQL Server 2000 only)	Allows a user to create user-defined functions. This permission is valid in every database.
create rule	Allows a user to create rules, mostly used for backward compatibility with SQL Server 6.x. This permission is valid in every database.
create default	Allows a user to create defaults—again, mostly for backward compatibility with SQL Server 6.x. This permission is valid in every database.
backup database	Allows a user to back up a database or a subset of files within the database. This permission is valid in every database.
backup log	Allows a user to back up the transaction log of a database. This permission is valid in every database.

Object Permissions

Object permissions allow you to grant access to specific database objects. By default, you can grant any permission available for objects that you create, including those described in Table 6-3.

Table 6-3. SQL Server object permissions.

Permission	Comments
select	Allows the user to read from a table or view
insert	Allows the user to add one or more rows of data to a table or view
update	Allows the user to modify one or more rows of data in a table or view
delete	Allows the user to remove one or more rows of data from a table or view
references	Allows the user to set a foreign key constraint against a table or to use schema binding against a function, view, or table
execute	Allows the user to run a stored procedure or user-defined function

Grant, Revoke, and Deny

Now that you know what kind of permissions there are to give, let's examine the three types of security statements: grant, revoke, and deny.

The grant statement

The grant statement is what you use to give a permission. To grant a statement permission, you'd issue something like this:

 grant create table to [EXAIR\Michael] 

To grant an object permission, you'd run something like

 grant select on mytable to [EXAIR\Michael] 

See the SQL Server Books Online for syntax and additional details for these statements.

The deny statement

The deny statement for permissions works much like a deny right does for integrated Windows logins. A deny is always preventative—any deny right will always override any grant. To deny rights, you'd run something like

 deny create table to [EXAIR\Accounting] 

or

 deny insert on mytable to [EXAIR\Michael] 

The revoke statement

The revoke statement takes away an existing grant or deny entry, much as it does with Windows integrated logins. You'd use it just like grant or deny. Here are some examples:

 revoke create table from [EXAIR\Michael] revoke select on mytable from [EXAIR\Michael] 

Object Ownership Chains

Object ownership chains are an important security consideration in SQL Server databases. In a common scenario, you don't want to give direct access to your tables to users. This scenario is addressed to some extent by the application role concept. Another way to do this is to grant users access via only stored procedures, functions, and views. SQL Server makes this practical with object ownership chains. Each object in SQL Server has an owner—the user who created the object, just like in Windows 2000. Object ownership can be reassigned by sysadmin, db_owner, or db_ddladmin. If an object references another object and each object has the same object owner, no permissions check is made on the dependent object.

For example, if a view on a payroll table doesn't include the salary column, users can be granted select rights on the payroll view. They do not need select permissions on the payroll table as long as the object owner for both the payroll table and the payroll view are identical. Then you can grant access to the salary column to a subset of users or allow access via an application role only.

If at any point in the object ownership chain the object owner changes, a permissions check will be performed and the appropriate permissions will be checked. For example, if I create a stored procedure that inserts into the payroll table but the payroll table has a different object owner, anyone trying to execute the stored procedure will also have to have insert rights into the payroll table. You must consider this behavior when designing your database and object owners.

Run a Security Audit in SQL Server 2000

SQL Server 2000 introduces full audit capability via the SQL Server Profiler. You should enable auditing for SQL Server security events so that you know when key events happen. Turning on full auditing can create massive amounts of data, so you might want to limit your auditing to logins, administrative actions, and failed operations.

You can set up C2 audit mode with the following command:

 exec sp_configure 'C2 Audit Mode', 1 go reconfigure go 

Then restart the SQL Server service. When SQL Server is restarted, all permissions audits will be written to a file named in the form audit_YYYYMMDDHHMMSS_<SequenceNumber>—for example, audit_200001071401000001_1.trc. YYYYMMDDHHMMSS is the time the file was created.

To turn off C2 audit mode, run the following commands:

 exec sp_configure 'C2 Audit Mode',0 go reconfigure go 

You can also use the SQL Server Profiler to perform a security audit with the user interface, as shown in Figure 6-5.

Figure 6-5. Using the SQL Server Profiler to audit security events.

If you want this trace—that is, the auditing of specified events—to run at all times, create a stored procedure that turns on the trace as you'd like to configure it and then set the stored procedure to be run at service startup. The example in the next section creates a stored procedure that monitors some auditing events and then sets the stored procedure to start the next time SQL Server is started. It also then immediately starts the stored procedure, which in turn starts the trace.

Please see SQL Server 2000 Books Online for details of which event numbers and columns are present in SQL Server.

Example audit stored procedure

The following is an example SQL stored procedure that will audit SQL Server even if the server is restarted. You'll notice that the sp_trace_setevent system stored procedure is used throughout; here's the syntax for sp_trace_setevent:

 sp_trace_setevent [ @traceid = ] trace_id , [ @eventid = ] event_id , [ @columnid = ] column_id , [ @on = ] 'on' or 'off' 

Table 6-4 describes the sp_trace_setevent arguments.

Table 6-4. The sp_trace_setevnt argument descriptions.

Argument	Comments
@traceid	The trace id assigned to the trace by the server.
@eventid	The event ID for the event—see SQL Server 2000 Books Online for the complete list.
@columnid	The column ID for the column you want to show—see SQL Server 2000 Books Online for the complete list.
@on	Turns on the column in the trace.
@off	Turns off the column in the trace.

Now for the example code:

 use master go drop proc p_audittrace go create proc p_audittrace with encryption as -- declare variables. declare @traceid int -- The ID of the audit trace. -- Create the trace container, and -- get trace id from the server. exec sp_trace_create @traceid output, -- Rollover file (2), shutdown server if audit can't be -- maintained (4). 6, -- Location of the file - note it's a Unicode string, -- hence the 'N'. N'd:\program files\microsoft sql server\mssql$sql2000\ audit\myaudit', -- Each file will reach 500 MB. 500 -- Let's just add backup/restore (115), and object permissions -- grants (103) add the backup/restore entry. exec sp_trace_setevent @traceid, --trace number 115, --login/logout 27, -- the event class column 1 -- turn it on -- Turn on the subevent class (1=backup, 2=restore). exec sp_trace_setevent @traceid, 115, 21, 1 -- Turn on the success/failure flag (1=success, 0=failed). exec sp_trace_setevent @traceid, 115, 23, 1 -- Turn on the servername (i.e., this SQL server). exec sp_trace_setevent @traceid, 115, 26, 1 -- Turn on the datetime (choose start time just because...). exec sp_trace_setevent @traceid, 115, 14, 1 -- Turn on the appname field. exec sp_trace_setevent @traceid, 115, 10, 1 -- Turn on the Windows NT username column. exec sp_trace_setevent @traceid, 115, 6, 1 -- Turn on the server SPID column. exec sp_trace_setevent @traceid, 115, 12, 1 -- Turn on the computername that the user's working at. exec sp_trace_setevent @traceid, 115, 8, 1 -- Turn on the SID the login has. exec sp_trace_setevent @traceid, 115, 43, 1 -- Turn on the login name -- (will be different than Windows NT username if -- standard security enabled). exec sp_trace_setevent @traceid, 115, 42, 1 -- Turn on the database ID that the backup/restore -- is running in. exec sp_trace_setevent @traceid, 115, 3, 1 -- Turn on the name of the database as well. exec sp_trace_setevent @traceid, 115, 35, 1 -- Turn on the database user name that's running -- the command. exec sp_trace_setevent @traceid, 115, 11, 1 -- Let's capture the text of the backup/restore -- statements as well. exec sp_trace_setevent @traceid, 115, 1, 1 -- Add the object permission grants. exec sp_trace_setevent @traceid, --trace number 103, --Login/logout. 27, -- The event class column. 1 -- Turn it on. -- Turn on the subevent class (1=grant, 2=revoke, -- 3=deny). exec sp_trace_setevent @traceid, 103, 21, 1 -- Turn on the success/failure flag (1=success, -- 0=failed). exec sp_trace_setevent @traceid, 103, 23, 1 -- Turn on the servername (i.e., this SQL server). exec sp_trace_setevent @traceid, 103, 26, 1 -- Turn on the datetime (choose start time just because...). exec sp_trace_setevent @traceid, 103, 14, 1 -- Turn on the appname field. exec sp_trace_setevent @traceid, 103, 10, 1 -- Turn on the Windows NT username column. exec sp_trace_setevent @traceid, 103, 6, 1 -- Turn on the server SPID column. exec sp_trace_setevent @traceid, 103, 12, 1 -- Turn on the computername that the user's working at. exec sp_trace_setevent @traceid, 103, 8, 1 -- Turn on the SID the login has. exec sp_trace_setevent @traceid, 103, 43, 1 -- Turn on the login name -- (will be different than Windows NT username -- if standard security enabled). exec sp_trace_setevent @traceid, 103, 42, 1 -- Turn on the database ID that the grant is run in. exec sp_trace_setevent @traceid, 103, 3, 1 -- Turn on the name of the database as well. exec sp_trace_setevent @traceid, 103, 35, 1 -- Turn on the database user name that's -- running the command. exec sp_trace_setevent @traceid, 103, 11, 1 -- Show the permissions type. exec sp_trace_setevent @traceid, 103, 19, 1 -- Let's capture the text of the -- grant/revoke/deny statements. exec sp_trace_setevent @traceid, 103, 1, 1 -- Start the trace. exec sp_trace_setstatus @traceid, 1 return go /* To mark this as startup so that it would run each -- time the server starts, run the following command. */ exec sp_procoption 'p_audittrace', 'startup', 'true' -- Start the trace now. exec p_audittrace