SQL Server Authorization

SQL Server has a rich authorization model. You can assign permissions to any SQL server object that performs an action or touches data in a database: stored procedures, views, columns, and tables. Within individual tables, you can, with some effort, assign privileges to individual columns and rows. Most applications need to apply one or more of the three types of table authorization:

• Table Level Each user has specific read or write access to the contents of an entire table. This can be set up by using the Properties dialog box of each table object from within SQL Server Enterprise Manager.

• Column Level Each user has specific read or write access to a particular column (or field) of a table. These permissions can also be set using the Properties dialog box of each table object from within SQL Server Enterprise Manager.

• Row Level Each user has specific rights to one or more rows within a given table. For example, a salesperson might only have rights to view information for her particular customer. The only way to apply row-level security is to set permissions so that only a view or stored procedure can access the data. This view or stored procedure would include logic that restricts what the user can access.

These three authorization mechanisms allow you to assign rights down to a particular cell of a table. To implement row-level security, you should deny users access to a table, but allow access to a view or stored procedure that accesses the table. This requires more work when designing the database, but it provides the ultimate in flexibility. SQL Server uses permission chains to evaluate whether a user can access a table through a stored procedure. If the owner of a table denies access to a table but subsequently allows access through a stored procedure, SQL Server allows the user to access the table through the stored procedure. For information on setting permissions, see the TechNet article at http://www.microsoft.com/technet/prodtechnol/sql/deploy/confeat/c05ppcsq.asp .