Clarifying the Concepts of SQL Server 2008 Schema and Fixed Roles

You must noticed in the SSMS, every database have some default schema(dbo, db_dbdatawriter etc.), default roles (db_owner, db_datawriter etc.), they seems to match each other and if you assign the user to db_datawriter, seems the user automatically has the write permission to the tables no matter this table reside in which schema, is that the initial design as we see, does those default schema has to be there, should we put some tables into the default schema’s such as db_datareader schema? Actually the answers are no, if you want, you even can change the db_datareader role to have write permission. Confused? Let talk from the basic concepts.

Schema:

Beginning with SQL Server 2005, Microsoft introduced the concept of database schemas. A schema is now an independent entity- a container of objects distinct from the user who created those objects. Previously, the terms ‘user’ and ‘database object owner’ meant one and the same thing, but now the two are separate.

Perhaps an example may better illustrate the concept: In SQL Server 2000, a schema was owned by, and was inextricably linked to, only one database principal (a principal is any entity or object that has access to SQL Server resources, for example a user, role or a group). This meant that if, say, a user creates a table in the database, that user cannot be deleted without deleting the table or first transferring it to another user. But in SQL Server 2005/2008 one can now simply create the table first and attach it to a schema, even without having created the user. This can be accomplished via the Transact-SQL statement below:

CREATE TABLE MySchema.MyTable (col1 int, col2 int)

Note that here ‘MySchema’ refers to the schema that owns ‘MyTable’, as contrasted to SQL Server 2000 in which for the same statement, ‘MySchema’ would have referred to the user who owns the table. This separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.

Default Schema

Because objects are no longer tied to the user creating them, users can now be defined with a default schema. The default schema is the first schema that is searched when resolving unqualified object names. The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema.

Apart from the obvious benefit that objects can now be manipulated independently of users, usage of schemas also offers the following advantages:

Managing logical entities in one physical database: we can think of schemas as containers to organize objects. If you take a look at the AdventureWorks sample database, you will see that the tables are organized by department or function, such as “HumanResources” or “Production”. This looks similar to the old owner concept, but has many advantages. First of all, since the objects are not tied to any user accounts, you do not have to worry about changing the owner of objects when an account is to be removed. Another advantage is that the schemas can be used to simplify managing permissions on tables and other objects. The schema has an owner, but the owner is not tied to the name. So, if an account owns a schema and the account must be removed from the database, the owner of the schema can be changed without breaking any code. If you do not wish to organize your database objects into schemas, the dbo schema is available.

Object protection: Through schemas, a DBA can control access to crucial objects that would otherwise be open to potentially destructive changes by the users.

Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself. Using schemas, a developer can logically group objects and even create custom objects without running the risk of messing up the underlying ISV database.

It is important note that if the user is authenticated by SQL Server via the Windows operating system, no default schema will be associated with the user. Therefore if the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema, though not directly with the user.

SQL Server fixed database roles

I assuming everybody understand the basic concept of DB roles and users, so just ignore that part and just discuss the confused fixed db roles which existing in every database. First thing to mention is those db roles are independent of the schema, it means when the user is belong to this role, this user has the role permission on all schemas. Second thing, when you assign the role to schema, you will see “Grant” and “With Grant”, former one means the user get the permission but cannot grant to others this permission, the latter one means after the user get the permission, it can grant to others the same permission.

The fixed database roles are:

db_owner
db_securityadmin
db_accessadmin
db_backupoperator
db_ddladmin
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter

db_owner: 

A member of the db_owner role can do anything inside the database. Now there is a difference between a member of the db_owner role and the dbo user. That difference is that if someone maps into the database as the dbo user, that person bypasses all security checks. An example of this is anyone who is a member of the sysadmin fixed server role. They map in as dbo. And as a result, they don’t receive security checks.If a user is not dbo but is a member of the db_owner role, it does receive a security check. Of course, unless you’ve explicitly used DENY to block access, that user can do what he or she wants. The DENY stops them cold (it does not stop dbo). However, a member of the db_owner role could remove the DENY, so effectively that person can do anything, even if you put roadblocks in place.

  • The db_owner role allows a user to do anything within the database.
  • DBAs who are already members of the sysadmin fixed server role come in as dbo and don’t need this role explicitly granted to them.
  • Normal users should not be a member of this role.
  • Applications might require their user account to be a member of this role.

db_securityadmin

Like the securityadmin fixed server role, the db_securityadmin fixed database role manages security.

  • The db_securityadmin role can manage role membership and permissions on securables.
  • Since DBAs usually manage security and are usually coming in as dbo, this role is little used.
  • Normal users should not be a member of this role.
  • Applications should tend not to need this role.
  • Since it’s little used, you should audit its membership for exceptions.

db_accessadmin

The db_accessadmin role also manages security, but handles access to the database. The db_accessadmin role grants, denies, or revokes permission to enter the database for logins. Combined with db_securityadmin, and you can completely manage security into and throughout the database.

  • The db_accessadmin role can allow access into or block access to the database for logins.
  • Since DBAs usually manage security and have an appropriate server-level role, this role is little used.
  • Normal users should not be a member of this role.
  • Applications should tend not to need this role.
  • This is another role you should audit for membership exceptions.

db_backupoperator

The db_backupoperator allows a member of the role to take backups of the database. However, it’s only going to allow native backups, as in the standard backups through SQL Server itself. If you’re using a third party product, chances are it is usually the methods which allow for high speed backups. Unfortunately, these methods require the login executing them to be a member of the sysadmin fixed server role. As a result, this role tends to be of limited usefulness. Add to it that you’re backing up to a local drive, and it’s rare to see a non-DBA having this level of access, even in a development system.

  • The db_backupoperator role allows a user to take backups of the database.
  • Most 3rd party backup utilities utilize methods that require sysadmin rights, which this doesn’t give.
  • Another role that is little used because this functionality is usually handled by DBAs or a service account.
  • Normal users should not be a member of this role.
  • Applications should tend not to need this role, though I have seen exceptions.

db_ddladmin

The db_ddladmin is another powerful role because it allows a user to create, drop, or modify any objects within a database, regardless of who owns it. So a user could alter a stored procedure owned by dbo, for instance. This role is sometimes given to developers on non-production systems as they built custom applications. However, there is typically no reason anyone should be a member of this role on a production database. One thing the db_ddladmin does not do is allow the user to alter permissions on the objects. So a member of this role can create or modify the object, such as a stored procedure, but not alter the permissions on it unless he or she is the owner.  So, for instance, a member of this role could create a stored procedure in a schema owned by dbo, but couldn’t grant the ability to execute it.

  • The db_ddladmin role can create, drop, and alter objects within the database, regardless of who the owner is.
  • The db_ddladmin role cannot alter security.
  • It is not unusual to grant this role to developers in a non-production environment.
  • Normal users should not be a member of this role.
  • Applications should not need this role.
  • No one should normally be a member of this role on a production database.

db_datareader

The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database. DENY for a user (or a role the user is a member of) will still block the SELECT, however. But if there are no permissions set, whatsoever, the user will have the ability to SELECT against the table or view. The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you’ll have to query the membership of this group via the use of sp_helprolemember:

EXEC sp_helprolemember 'db_datareader';

It is not unusual to see the db_datareader role used in databases. It’s an easy way to grant SELECT permissions to everything without having to worry about it. However, due to the fact that it uses implicit permissions, I prefer to create a user-defined database role and explicitly grant permissions. With that said, here are things to remember:

  • The db_datareader role gives implicit access to SELECT against all tables and views in a database.
  • In SQL Server 2005 and up, an explicit DENY will block access to objects.
  • It is not unusual to see this role used in production for developers.
  • It is not unusual to see this role used in production for normal users.
  • Applications will occasionally need this role.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_datawriter

The db_datawriter role is like the db_datareader role in that it gives implicit access to tables and views within a database. It also can be blocked by an explicit DENY for the user or for a role the user is a member of. Unlike db_datareader, however, db_datawriter gives INSERT, UPDATE, and DELETE permissions . Again, since the permission is implicit, you will not see these rights show up in sys.database_permissions. And like with db_datareader, you’ll have to check the membership of this role to determine actual permissions in the event of an audit.

  • The db_datawriter role gives implicit access to INSERT, UPDATE, and DELETE against all tables and views in a database.
  • In SQL Server 2005 and up, an explicit DENY will block access to objects.
  • Typically developer are not members of this role in production unless all users are.
  • While less common than with db_datareader, it is not all that unusual to see this role used in production for normal users.
  • Applications will occasionally need this role.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_denydatareader

Unlike the previous two roles, db_denydatareader denies access. In this case, the db_denydatareader is the same as having a DENY for SELECT on all tables and views in the database. Because DENY trumps everything else, this is not a role I’ve seen used frequently. If there are no permissions for a given user on an object, such as the user has no SELECT permissions on a table, then SQL Server blocks access. Therefore, if a user doesn’t have SELECT permission on TableA, then the user cannot successfully issue a SELECT query against TableA. An explicit DENY is not needed. And since this affects all tables and views, that adds to the reason this database role is typically not used. And like db_datareader and db_datawriter, the DENY is implicit, meaning you’ll have to query for membership in this role to determine who is affected.

  • The db_denydatareader role is denied access to SELECT against any table or view in the database.
  • Typically this role is not used.
  • The DENY is implicit.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_denydatawriter

Wrapping up our list of roles is db_denydatawriter. The db_denydatawriter has an implicit DENY on INSERT, UPDATE, and DELETE for all tables and views in the database. Again, this is not a role that sees much use, for the same reasons as db_denydatareader.

  • The db_denydatawriter role is denied access to INSERT, UPDATE, or DELETE against all tables and views in the database.
  • Typically this role is not used.
  • The DENY is implicit.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

Discussion of the relationship

Ownership of schemas and schema-owned objects is transferable. This is accomplished using the ALTER AUTHORIZATION command. A single schema can contain objects owned by multiple database users or roles. Multiple database users can share a single default schema. Permissions on schemas and schema-contained objects can be managed with greater precision than in earlier releases. This is accomplished using schema GRANT permissions object GRANT permissions. A schema can be owned by any database principal. This includes roles and application roles. A database user can be dropped without dropping objects in a corresponding schema.

Those default schemas such as db_datareader, db_datawriter etc. are for backwards compatibility reasons. In SQL Server 2000, schema is as the same as the user and the schema must be created by the corresponding user. So if migrated from 2000, it must be match each other otherwise there is an error.  But the schema and fixed roles are not necessary to be there. The database fixed roles such as “db_datareader” are owned by dbo. But the schemas are ownded by corresponding roles. Both of the above are re-configurable.

“db_datareader” and “db_datawriter” can perform SELECT/INSERT/DELETE/UPDATE operation on any table or view with any schema which due to “Implicit Permissions” of fixed roles. But in SSMS, for an user with db_datareader role, he can only view the tables/views with the schema db_datareader or dbo or other schemas owned by db_datareader or dbo or the user itself.

Some reference:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s