New Security Features in SQL Server 2005 - dummies

New Security Features in SQL Server 2005

By Andrew Watt

Part of Microsoft SQL Server 2005 For Dummies Cheat Sheet

Microsoft SQL Server 2005 has several new security features compared to the SQL Server 2000 security model. The SQL Server 2005 security model enables you to both assure security and make SQL Server easy to use for authorized users and customers.

Here’s a look at new or updated security features in SQL Server 2005:

  • Logins: Logins are (SQL Server) instance-level principals.

  • Users: Users are database-level principals.

  • Separation of users and schemas: Each schema has a user who is the owner of the schema. A schema is the owner of the objects in the schema. It is possible to change the owner of a schema without having to change any application code that uses objects in that schema. This avoids the problems that occur if a user who owns database objects, for example, leaves the company.

  • Catalog security: Metadata is visible only for the tables that a user has permissions on. This helps to hide unauthorized information from users.

  • Module execution context: This supplements Ownership Chaining that was present in SQL Server 2000.

  • Granular permissions control: Granting of permissions is more granular than in SQL Server 2000. This means that you can now use lower privilege accounts to do some tasks for which you needed to use an administrator account in SQL Server 2000.

  • Password policy enforcement: If you run SQL Server 2005 on Windows 2003 Server, you have the option to enforce in SQL Server any password policy that already exists for the Windows user accounts.