Granting permissions in SQL Server 2005

Imagine for a moment that you're a SQL Server 2000 production DBA (unless, of course, you are). You arrive at the office one morning, get a hot drink and check your e-mail. There are two new co-worker requests in your inbox: Bob in purchasing needs read-only access to all of the sales data so he can run reports to predict upcoming material acquisition volumes; John in Web development has a problem with a production Web application and needs access in order to run a Profiler trace to identify the problem. These seem like easy requests to fulfill -- or are they?

In the first case, sales data is stored in over 120 of the 400 tables in the production database. You have a role established for members of the sales team that grants both SELECT and UPDATE permissions, but Bob only needs permission to read data. This leaves you with only one option: Grant Bob SELECT access to each table, one by one.

Feel the carpal tunnel syndrome setting in yet?

As for the access request to run Profiler traces -- no can do. In SQL Server 2000, access to run a trace is granted only to members of the Systems Administrators (sysadmin) fixed server role. Assigning John to that role would give him complete access to everything on the server -- probably not a good idea. This leaves you with only one option: Work with John and run the trace yourself, tacking more tasks onto your already tight schedule.

Neither scenario is alien to any regular SQL Server 2000 DBA, but take solace -- a break is coming. SQL Server 2005 will not only offer various programmatic enhancements, it will also sport various security model improvements. A couple of these solve both Bob's and John's dilemmas: user-schema separation and granular server permissions.

TABLE OF CONTENTS    New feature: User-schema separation    New feature: Granular server permissions    New function: View available permissions

User-schema separation

Return to Table of Contents

SQL Server 2005 adds support for schemas, as described in the ANSI SQL-92 specification. Think of schemas as logical containers for database objects. These containers replace the way that many DBAs used object owners in previous versions of SQL Server. In SQL Server 2005, objects no longer have an owner. Instead, objects are members of a schema, and are accessed by schema name.

Let me compare. In previous versions of SQL Server, the following three-part naming system applied:

database_name.owner_name.object_name

In SQL Server 2005, this system is used:

database_name.schema_name.object_name

In SQL Server 2005, instead of granting permissions to objects directly, DBAs can choose to grant permissions to schemas. Granting permissions to a schema propagates permissions to every object currently contained in the schema and any objects added to the schema in the future, meaning that the DBA does not need to update permissions for every user any time a new object is added.

This level of indirection between users and objects has these key advantages:

1. Since objects are no longer owned by users, users can be dropped without dropping or reassigning any objects they own. This saves you a headache when employees leave, for instance.

2. Objects in the same database can now be grouped logically based on business function. For instance, you may wish to populate a client management database with both client contact information and sales data. The tables that make up each function are independent for the most part. In previous versions of SQL Server, you may have had to create two databases. While that logically groups the objects, it makes them impossible to relate using foreign key constraints, which, consequently makes referential integrity difficult.

Schemas are created using the CREATE SCHEMA T-SQL statement. For instance, a schema called Sales can be created using the following T-SQL:

CREATE SCHEMA Sales

Once a schema is created, you can create objects within it by simply prefixing with the schema name. For instance, to create a table in the Sales schema called MySalesData, the following T-SQL could be used:

CREATE TABLE Sales.MySalesData
(
    SalesDataId INT NOT NULL PRIMARY KEY,
    SalesAmount DECIMAL(9,2) NOT NULL
)

Objects can also be transferred among schemas using the TRANSFER option of the ALTER SCHEMA statement. The MySalesData table could be moved to a schema called OtherSchema using the following T-SQL:

CREATE SCHEMA OtherSchema
GO
ALTER SCHEMA OtherSchema TRANSFER Sales.MySalesData
GO

Once schemas are created and populated, users can be assigned permissions to every object in the schema simultaneously. For instance, to assign a user called Bob SELECT access to all objects in the Sales schema, the following T-SQL could be used:

GRANT SELECT ON Sales TO BOB

This is clearly more efficient than the equivalent method in previous versions of SQL Server -- having to assign permissions to every object one by one.

New feature: Granular server permissions

Return to Table of Contents

Using the user-schema separation feature makes assigning permissions to database objects en masse quite simple. What about the assignment of server-level permissions?

SQL Server versions up to and including SQL Server 2005 ship with fixed-server roles, collections of permissions to various server resources. If you want to grant login permissions to create, alter or drop databases, you can make that login a member of the "dbcreator" server role. But, if you want to grant login access only to create databases, you would not have been able to in previous versions of SQL Server. In SQL Server 2005, the fixed roles have been broken down into their component parts, which can be granted via the GRANT statement. For instance, to grant a login called Bob access to create databases on the server, the following T-SQL can be used:

GRANT CREATE ANY DATABASE TO Bob

There are many other server-level permissions available, all of which are listed in the SQL Server 2005 Books Online topic, GRANT Server Permissions. By far the most exciting permission that can be granted is the ability to run traces. Remember John, the Web developer who needed access to run traces on the production server? In SQL Server 2000, this would have required access to the Server Administrators' fixed-server role. In SQL Server 2005, the following T-SQL will do the trick:

GRANT ALTER TRACE TO John

This feature alone should put a grin on the face of most production DBAs. There is no more need to add developers to the Systems Administrator role just so they can run traces -- and there's no need to do work for them since they shouldn't be added to that role to begin with.

New function: View available permissions

Return to Table of Contents

All of these new permissions and methods for assigning permissions can make things confusing. Users may not understand what permissions they do or do not actually have. Luckily, Microsoft has provided a new function to deal with these issues: fn_my_permissions(). This function returns information about which permissions are available to the user currently logged in or impersonated.

The fn_my_permissions() function has two parameters: a target name and target type described by the name. For instance, if the target is the schema called Sales, the target name is SCHEMA and the following T-SQL would be used to return a table of permissions:

SELECT *
FROM fn_my_permissions('Sales', 'SCHEMA')

To drill down further and request information on permissions to the MySalesData table, this T-SQL would be used:

SELECT *
FROM fn_my_permissions('MySalesData', 'TABLE')

Virtually every object type can be specified as a target. To request server-level permissions, use NULL for both parameters, as follows:

SELECT *
FROM fn_my_permissions(NULL, NULL)

Live the easy life

So let's recap. The new permissions features in SQL Server 2005 give you more control over resources and less to worry about on a day-to-day basis. User-schema separation makes managing large databases much simpler. The ability to grant permissions on a more granular basis means that you will be able to let users do some of their own tasks, thereby offloading responsibility. Both features should make your life -- and users' lives -- much simpler. And isn't that everyone's definition of the Easy Life?

About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.

More information from SearchSQLServer.com