SQL Server vastly improves file-level security

If you are still working on SQL Server 2000 (as many of our customers are), here's another reason to upgrade to a newer version. One area that has improved vastly is file-level security. SQL Server 2005 introduced inheritance of permissions. Let’s take a look.

In SQL Server 2000, if all you wanted to do was to give Execute permissions to a set of stored procedures in a database to a particular user or role, you had to grant that permission to each and every stored procedure individually. This was a pain.

In SQL Server 2005, we now have inheritance of permissions. This means that we can set permissions at four main levels. The levels follow the four-part naming convention of database objects: Server.Database.Schema.Object . For a table you can also set permissions at the column level.

So now we can still set permissions at the individual object level for tables, views, stored procedures etc, but we can now set permissions at the higher levels of schema, database or server level. The new schema object allows us to group objects together within a database using logical names such as Accounting or Marketing. The default schema is dbo for backward compatibility.

The model is very similar to NTFS file security with drives, folders, sub-folders and files. You can set permissions at each level with inheritance applying the permissions to lower levels. The effective permissions for a user is the accumulation of all the permissions with Deny trumping a Grant permission if there is a conflict. SSMS has a nice “Effective Permissions” button to help you unravel a set of multi-level permissions.

So now, if you want to grant permissions for a set of stored procedures in a database you can now grant Execute permissions at the Database level. Or you can group them into schemas and set permissions at the schema level.  In SSMS that can be a single check box. Job done.

Once again, Microsoft is giving us a choice.

Recent Posts:

Get rid of those NOLOCK hints…

Travel Budget cut? Think Virtual…

Develop Custom Reports in SSMS...

Universal Health Care for SQL Server? SQL Profiler - 2008 style...

Related:

Copyright © 2009 IDG Communications, Inc.

The 10 most powerful companies in enterprise networking 2022