In a previous article, SQL Server Security: Server Roles, I covered the fixed server roles included with SQL Server 2000. Another set of roles that are available to us are fixed database roles. As the name would imply, these are at the database level only. Like with fixed server roles, some of the fixed database roles, such as db_accessadmin and db_securityadmin, are designed to assist a DBA with delegating administrative responsibilities. Others, such as db_datareader and db_datawriter, are designed to provide blanket permissions for an end user.

Here are the fixed database roles available in SQL Server 2000:

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin
• public

Just as there is a system stored procedure to return fixed server roles, there is also one for fixed database roles. The system stored procedure sp_helpdbfixedrole will return a list of all fixed database roles, with the exception of the public role. While the public role is a fixed database role and it does show up in Enterprise Manager, it doesn’t appear in the list this stored procedure returns (nor does it appear in the list in Books Online). Just remember to include it mentally. Another system stored procedure, sp_helprole, can be used to list all of the database roles for a given database and includes a column to indicate whether or not the role is an application role (which I've discussed the good and bad of in SQL Server Security: Pros and Cons of Application Roles). To list permissions a particular fixed database role has, we have at our disposal the system stored procedure sp_dbfixedrolepermission. This stored procedure isn’t necessarily 100% accurate, just as with sp_srvrolepermission. It gets most of them, but when in doubt, refer to Books Online.

The db_accessadmin role

The db_accessadmin fixed database role is akin to the securityadmin fixed server role: it has the ability to add and remove user access to the database just as the securityadmin has the ability to add and remove logins to the server. The db_accessadmin role does not, however, have the ability to create or remove database roles, nor does it have the ability to manage permissions. Using the

sp_dbfixedrolepermission system stored procedure, I can obtain the list of permissions granted to the db_accessadmin role:

• sp_addalias
• sp_dropalias
• sp_dropuser
• sp_grantdbaccess
• sp_revokedbaccess

As expected, I don’t see anything except for stored procedures directly related to adding and removing users from a database. Do notice that sp_adduser is not in the list of permissions. The system stored procedure sp_adduser has a parameter, @grpname, that sp_grantdbaccess does not. Therefore, sp_adduser not only adds a user to the database, but it also can make that user being created a member of a particular database role. Here the line is crossed from just giving access to the database to also managing permissions. Since the db_accessadmin role is supposed to just manage database access and not touch permissions, the sp_adduser stored procedure is too powerful. As a result, the db_accessadmin role doesn’t have the ability to execute it.

Tip: To give a user the ability to add users to the database AND manage roles and permissions, make the user a member of both the db_accessadmin role and the db_securityadmin role (which I’ll discuss shortly).

The db_backupoperator role

The whole purpose of the db_backupoperator is to have sufficient rights to create backups of a database. Restore permissions, however, are not granted to the db_backupoperator role.

The db_backupoperator also has the ability to execute the CHECKPOINT command. This command forces any dirty pages to be written to disk. According to Books Online, this can minimize the recovery time since all operations are guaranteed to be written to disk and SQL Server doesn’t have to reapply any changes that might have been in memory and didn’t get written to disk. Also, if the database is in simple recovery mode, a CHECKPOINT command will cause the transaction log to truncate. With that said, I cannot remember a time when I’ve actively used the CHECKPOINT command. One of the main reasons why is when SQL Server is shutdown in an orderly manner (i.e. not a server crash or a case where SQL Server was brought down with the command SHUTDOWN WITH NO WAIT), it will automatically issue a CHECKPOINT against each database to minimize the recovery time during the next SQL Server startup. As a result, there’s often not a need to execute the CHECKPOINT command.

I will say that I don’t use this role a whole lot. Most of the backup jobs I run are controlled by SQL Server Agent or an external job scheduler and write backups to disk. These backups are then grabbed by a third-party product backup product and written to tape. Since the SQL Server Agent or job scheduler has sysadmin level access, I don’t have much need for this particular role. The reason I use automated processes like SQL Server Agent is to generate the proper backups on a regular schedule. If I have a junior DBA manually executing backups and he or she forgets one, it’ll probably end up being the very backup I need later on. Better to have it done automatically.

The db_datareader role

The db_datareader role has the ability to run a SELECT statement against any table or view in the database. This role is often used in reporting databases where users would be coming in via a third-party reporting tool and building their own ad-hoc queries. If you need to restrict a user to only be able to read from certain tables, the db_datareader is not the right choice as it would have to be combined with the explicit use of DENY permissions on tables the user shouldn’t be able to access. A better practice would be to create a user-defined database role with the proper permissions.

One key point about the db_datareader role is that it always has the right to read all tables and views. That means if you create a new table in the database, a member of the db_datareader role has access immediately. This differs greatly from a user-defined role with which you must explicitly grant each permission. Therefore, unless you give a user-defined role permission to access a database object such as a table or view, that role can’t do so. Not only does it have access to user tables but also system tables. That means a member of the db_datareader role can execute a SELECT query against a system table even you decide to revoke public access to SELECT against these tables (keep in mind that revoking default permissions would result in an unsupported configuration so far as Microsoft is concerned).

The db_datawriter role

The db_datawriter role has the ability to modify via INSERT, UPDATE, or DELETE data in any table or view in the database. This is like the db_datareader role having SELECT rights across all objects. Also like the db_datareader role, the db_datawriter role has permission to write to all tables and views, regardless of when they get created. So if you assign a user to the db_datawriter role and then create a table in the database, the user has access to modify the table immediately. Again, if specific permissions are needed, I recommend a user-defined database role as a better option.

The Lack of a db_executor Role

You’ve probably caught on to the fact that while there is a db_datareader and a db_datawriter role, both of which have direct access to the tables and views, there is no role that automatically has the ability to execute any stored procedure for a given database. The db_datareader and db_datawriter roles don’t have any special permissions for stored procedures and neither, for that matter, does any other fixed database role. At times I wish there was something akin to a db_executor role, a role that had the ability to execute any stored procedure in the database. Though I am leery of any situation where blanket permissions are given out, I do realize there are times when this is the best way to accomplish something.

For instance, an in-house application makes heavy use of stored procedures, but everyone has the ability to execute all stored procedures in the database. As changes are made to the application, a DBA has to continue to craft additional statements just to handle permissions. If there was some breakdown in stored procedure permissions where different users required differing permissions, then a an executor role wouldn't be helpful. But there are cases where every user in the database requires the ability to execute every stored procedure. In cases like this a db_executor role would come in handy and actually simplify security management.

I've covered how to manually create and keep up-to-date such a role in SQL Server Security: The db_executor Role.

The db_ddladmin role

A user with the db_ddladmin fixed database role has rights to issue Data Definition Language (DDL) statements in order to CREATE, DROP, or ALTER objects in the database. The ability to set permissions such as with DENY, GRANT, or REVOKE, however, is not given to the db_ddladmin role. If I list the rights of this role, SQL Server returns:

• All DDL but GRANT, REVOKE, DENY
• dbcc cleantable
• dbcc show_statistics
• dbcc showcontig
• REFERENCES permission on any table
• sp_changeobjectowner
• sp_fulltext_column
• sp_fulltext_table
• sp_recompile
• sp_rename
• sp_tableoption
• TRUNCATE TABLE

Of these permissions, sp_changeobjectowner has an additional requirement. In order for a member of the db_ddladmin role to be able to successfully call sp_changeobjectowner, the member must also be a member of the db_securityadmin database role. When an object’s owner is changed, permissions are changed as well. As a result, there is a check built into the system stored procedure to verify that a calling user is either a db_owner or the combination of a db_ddladmin and db_securityadmin:

-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
if (is_member('db_owner') = 0) and
(is_member('db_securityadmin') = 0 OR is_member('db_ddladmin') = 0)
begin
raiserror(15247,-1,-1)
return (1)
end

DBAs should exercise great caution with this database role. The db_ddladmin role has the ability to create objects under the context of any valid user. This means the role can create objects belonging to dbo simply by specifying the owner in the CREATE statement. The role doesn’t have to create an object under one user context and then use sp_changeobjectowner to accomplish this. So even if the db_ddladmin role isn’t paired with db_securityadmin, it does have the ability to modify objects for any user, to include dbo.

Note: Because of the ability to create an object with any owner, I would not recommend any user being granted this right in a production environment except in the rarest of circumstances. Even in development you should carefully weigh its use for personnel outside of the DBA team. This is a good security permission for a junior DBA who is only assigned development tasks, however. It ensures the DBA can create objects as needed, but can’t alter the database properties themselves (such as cross-database ownership chaining and other settings using sp_dboption).

The db_denydatareader role

The db_denydatareader role is the exact opposite of the db_datareader role: instead of granting SELECT permissions on any database object, the db_denydatareader denies SELECT permissions. If access to the database can be controlled completely via stored procedures (with no dynamic SQL queries), it may be a good idea to make standard users a member of this role. By placing a user into the db_denydatareader role, the user is unable to connect to the database and generate a SELECT query against any table. If the same user is granted execute rights to a stored procedure that controls how a particular table should be accessed, the user can only view data based on how the stored procedure is coded, meaning data access can be carefully controlled. For instance:

EXEC sp_addlogin 'testuser', '8ntEasyBeingCheezy!'
GO
USE Northwind
GO
EXEC sp_grantdbaccess 'testuser'
EXEC sp_addrolemember 'db_denydatareader', 'testuser'
GO
IF EXISTS (SELECT id FROM sysobjects WHERE Uid = USER_ID('dbo') AND [name] = 'usp_ControlSELECT')
BEGIN
DROP PROC dbo.usp_ControlSELECT
END
CREATE PROC dbo.usp_ControlSELECT
AS
SELECT CompanyName, ContactName, City, Country
FROM Customers
GO
GRANT EXECUTE ON dbo.usp_ControlSELECT TO testuser
GO

Here I’ve creating a user, testuser. I want to give this user the ability to see some, but not all of the information available in the Customers table. I do not want the user to be able to control how the data is selected. So once the user is granted access to the database, testuser is placed in the db_denydatareader role. This prevents the user account from successfully issuing a SELECT against the Customers table.

However, I still want the user to be able to see the CompanyName, ContactName, City, and Country in the Customers table and so I’ve created a stored procedure that controls this (this can also be done in a view). By granting the testuser login the ability to execute my new stored procedure, the user has the ability to view the data in the means I want. Should the user attempt to issue a SELECT statement, the user will receive the following error (Figure 1):

Figure 1. The testuser login is denied SELECT because of db_denydatareader.

Keep in mind that the public role has SELECT permissions against all tables in the default Northwind database configuration. Simply adding testuser to db_denydatareader has ensure the user cannot access the Customers table directly, even with the wide-open access granted to the public role. You can see what permissions are assigned to a particular object by using the sp_helprotect system stored procedure.

The db_denydatawriter role

Like db_denydatareader, the db_denydatawriter role serves to restrict permissions on a given database. With this role, the user is preventing from modifying the data on any data via an INSERT, UPDATE, or DELETE statement. Again, if access can be completely controlled by stored procedures, this is a good role to put a standard user into. This will insure the user only accesses the data in the means intended.

Tip: If data access can be controlled via stored procedures while maintaining ownership chains and completely avoiding dynamic SQL, consider using the db_denydatareader and db_denydatawriter role for regular users to restrict data access to the stored procedures only.

The db_owner role

Like the sysadmin fixed server role, the db_owner role has complete control, except in this case it is control of the database instead of the whole server. The db_owner can assign permissions, backup and restore (so long as the restore action doesn’t incidentally also cause a CREATE DATABASE operation such as restoring a database not on the server or restoring a database with replace) the database, create and destroy objects, and manage user accounts and roles without any restrictions. Like with the db_ddladmin role, DBAs should exercise caution before giving out the db_owner role, even in development. Give developers only what rights they need and no more.

Caution: It is a best practice to ensure rights, regardless of system, are the minimum required to do the job and nothing greater.

This is the Principle of Least Privilege. Even if a user has good track record of doing only what they are supposed to do, we must always expect the case where a user’s account is compromised. If a user has db_owner rights when such permissions are unnecessary, the person who compromised the account is capable of adding, altering, or removing data; modifying objects; or anything else he or she wants to do within that database.

Developers and db_owner Rights

Now for a real world example. In a development environment, several developers were granted db_owner rights at the request of their manager over the protests of the DBAs. One of the developers would be working in the late evening hours to accommodate personal issues. Since the DBA team was not around, the manager wanted the developer to have all the rights he would need to continue to work on a critical application. After another round of protests, the developer was granted db_owner rights, because it was deemed critical to the project for the developer to have such rights.

A couple of months later, other developers were noticing that certain tables, views, and stored procedures weren’t functioning as they thought they should. A quick comparison with the change control database showed these objects were different. After some investigation, it was found the developer working in the evenings had made changes but had not informed anyone. Other members of the development team had lost time because of the changes and this almost set an already late project behind two more weeks.

This example illustrates a potential issue with assigning the db_owner role to a non-DBA. The DBA can’t guarantee the schema of the database and if a developer makes changes without anyone else’s knowledge, the developer can cause other development team members to fall behind through no fault of their own. There are times when DBAs will be strong-armed into giving up db_owner rights and then the only thing the DBAs can do is make everyone aware of the potential issues.

One possible solution would have been for the developer to code his stored procedures on a local version of SQL Server such as SQL Server 2000 Developer Edition (which the developer had a license for due to a MSDN Universal subscription) or SQL Server 2000 Personal Edition. Then, after the stored procedures had been unit tested, they could have been applied using standard change control procedures which were in place on the project.

dbo Versus db_owner

In every database there is a special user, dbo. While dbo has the effective permissions of a database owner, it is not one and the same as the db_owner role. Any user account can be assigned to the db_owner role, giving that user complete control of the database. The dbo user account is a special account which all members of the sysadmin role are implicitly mapped to. Also, the system table sysdatabases (located in master) stores who is the owner of the database (and is assigned the dbo user account) in the sid field. So not only are sysadmins mapped to dbo, but so is the database owner. The dbo user will always have db_owner rights to a given database, but it is more than just a default user account.

The reason the sid of the database owner is important is in the case of a damaged database. Normally, a db_owner role member could restore a database, but the information on who belongs to the db_owner role is stored within the database itself. If the database is damaged, SQL Server won’t be able to determine who are members of the db_owner role. The only logins it knows for certain have the appropriate rights is the db_owner (by virtue of the information stored in sysdatabases) and those who are assigned to the sysadmin fixed server role (since they have all rights anyway). Any time SQL Server cannot obtain the information about the db_owner database role because the database is unavailable, the actual owner (apart from the sysadmins) will be the only one who can perform such functions.

Another difference deals with permissions. If a user is a member of the db_owner role but not the dbo, DENY permissions still apply. In other words, if I issue a DENY to prevent a particular user from executing a stored procedure (or selecting data from a table, etc.), the user will be unable to issue the execute statement (or SELECT, etc.) as SQL Server will prohibit the user from doing so. The dbo, however, will be able to carry out the command since the dbo naturally bypasses all permissions checks within the database.

The db_securityadmin role

The db_securityadmin role has rights to handle all permissions within a database. The full list is:

• DENY
• GRANT
• REVOKE
• sp_addapprole
• sp_addgroup
• sp_addrole
• sp_addrolemember
• sp_approlepassword
• sp_changegroup
• sp_changeobjectowner
• sp_dropapprole
• sp_dropgroup
• sp_droprole
• sp_droprolemember

The list includes the DENY, GRANT, and REVOKE commands along with all the store procedures for managing roles.

Aside from the permission commands, there is also the now familiar stored procedure, sp_changeobjectowner. Recall from my discussion of the db_ddladmin security role that sp_changeobjectowner requires either db_owner or the combination of db_ddladmin and db_securityadmin role membership. This is because the stored procedure makes a schema and a permission change.

Issues with Fixed Database Roles

Surprisingly enough, the db_securityadmin role cannot add users to the fixed database roles. Only db_owner role members can add users to a fixed database role and this is by design in SQL Server. If we take a look at the code for sp_addrolemember, the stored procedure for adding a user to any role, we'll find the following code:

-- CHECK PERMISSIONS --
-- Only member of db_owner can add members to db-fixed roles --
if (not is_member('db_owner') = 1) and
(not (@roluid < 16400 and is_member('db_owner') = 1)) and
(not (@roluid >= 16400 and is_member('db_securityadmin') = 1)) and
(not (@roluid >= 16400 and is_member(user_name(@owner)) = 1))
begin
dbcc auditevent (110, 1, 0, NULL, @membername, @rolename, NULL)
raiserror(15247,-1,-1)
return (1)
end
else
begin
dbcc auditevent (110, 1, 1, NULL, @membername, @rolename, NULL)
end

User defined database are numbered from 16400 on up. Fixed database roles are numbered from 16384 to 16399. Therefore, sp_addrolemember prohibits db_securityadmin role members from adding users directly to a fixed database role. A work around is to use nested roles. I can create a user defined database role, make it a member of the fixed database role, and the securityadmin can add to the user defined database role. Here's a solution to a question asked in the forums not too long ago:

-- Create user defined database role Readers
-- Add it to the db_datareader role
EXEC sp_addrole Readers
EXEC sp_addrolemember db_datareader, Readers
GO
-- Add users to Readers role
EXEC sp_addrolemember Readers, MyDatabaseUser

The public role

When a login is granted the ability to access the database, it's automatically placed in the public role, and so long as it has the ability to access the database, it will be in the public role (it can’t be removed). All users for a given database are part of the public role, regardless of other role membership. As a result, the public role has the default permissions for any user with access. So if all current and future users who have rights to a database should have the ability to access a particular stored procedure, rights could be assigned to the public role, such as in the following example:

GRANT EXECUTE ON dbo.EveryoneStoredProc TO public

The key word is “could.” Personally, I assign no permissions to the public role and I recommend strongly that you don’t either. I don’t know of anyone who would consider the use of the public role a best practice. Andy Warren has written against the use of the public role in his article Using the Public Role to Manage Permissions.

Perhaps a practical example will illustrate the aversion many DBAs have to using the public role. If I have a case where all users should access a particular object, I’ll create a user-defined role. My reason is simple: while everyone currently requires access to that particular object, this may not always be the case. If, in the future, a single user does not require permissions, I’ll be forced to uncouple the rights to the public role, create a new role, and assign the permissions to it. I’ll then be adding all the users except for the one in question to the new role. It’s much simpler if I go ahead and do this up front, rather than try and do it later after additional complexity may have been added.

If I decide uncoupling the rights granted to the public role is not the direction I want to take, I could leave the public role alone (which has access) and create a user-defined role and use DENY to keep the user from getting to the object. But now I’m stuck using DENY in order to restrict permissions. This approach complicates matters, both for the database and for me. Not only do I now have to remember what rights the public role has but I also have to remember what rights particular roles DENY. It is so much simpler to use user-defined database roles from the beginning and only grant permissions the role actually needs.

Caution: Since I’m talking about DENY, let me point out a big no-no: assigning DENY to the public role.

If you DENY public access to something, it doesn’t matter if you GRANT access at a later point. DENY is a trump: it overrides all other permissions. So if a user has been given permission through one role to SELECT from a table, but in another role (such as public) the user also has DENY SELECT, the DENY will take effect. Instead, remove all permissions from public. Then assign only the permissions that are necessary to the appropriate roles you create (don’t use public). Once you assign DENY to public, it applies to everyone except for special privilege accounts such as those that are members of the sysadmin fixed server role or whoever is the database owner. Even members of the db_owner fixed database role are affected.

As I mentioned earlier, the Northwind database, shipped as a sample with SQL Server, takes the opposite approach and utilizes the public role heavily. As Figure 2 shows, permissions to stored procedures, tables, and views have been assigned to this role as opposed to a user-defined database role.

Figure 2. Rights assigned to public role in Northwind database.

This can be a concern especially if we're looking at a database linked to a web site. If proper input validation isn't being done by the web app, SQL Injection becomes a possibility. In order for a SQL injection attack to work, the user account executing the query must have the appropriate rights.

Since the public role applies to all users, if I assign rights to it I am potentially opening myself up to a SQL injection attack if the application isn’t properly secured. Granted, even if I don’t use the public role to assign rights, a SQL Injection attack is going able to take advantage of whatever rights the user has access to. But consider the case where a database serves as a back-end to a web server (setting up the SQL Injection possibility) and also is used by the organization’s users. If I try and restrict access from the web server to only what is absolutely necessary for the web application to function properly but then I turn around and try and manage my organizational users through the public role, I’ll have created a real mess with respect to the security of the database. Since the public role applies to every user, that means my web users will also have the rights granted to the public role. So whatever permissions I tried to restrict the web users to will be basically out the window. Had I instead created a user-defined role and assigned my organization’s users to that role (and the permissions as well), the SQL Injection attack could only take advantage of the permissions granted to the web user(s).

The optimal security solution is to control all access through the use of stored procedures and take advantage of ownership chains, thereby ensuring queries which try to issue standard SQL commands such as SELECT, INSERT, UPDATE, or DELETE against tables and views are ineffective. This will limit the power of a SQL Injection attack, but keep in mind that the poor application design means whatever steps we take won’t stop everything. Also, there are times when the approach of using stored procedures and ownership chains heavily isn’t feasible (though I would come back and say this is a pretty rare occurrence that I’ve only seen in a handful of third party applications). But giving rights to the public role isn’t the answer, either. By utilizing the public role, we can inadvertently cause a security issue due to attacks such as SQL injection, as demonstrated by the scenario I described above.

Concluding Comments

Fixed database roles are a helpful addition to SQL Server 2000. Some roles, such as db_securityadmin, are included to assist DBAs with delegation of administration responsibilities for a given database. Other roles, such as db_datareader, are used to provide a simple method of assigning blanket permissions for end users. With that said, there is no role for executing all stored procedures in a given database. If you want something like this, you'll have to create it manually.

As with anything security related, be sure you understand the full scope of permissions for a role before implementing it in your security design. Certain roles, such as db_ddladmin and db_owner, have very broad permissions that typically aren't assigned in a production environment. Other roles, such as db_securityadmin, have limitations on the full scope of permissions they are supposed to have. However, the fixed database roles exist for our convenience and can be a key piece of our security models.

Additional Resources

• SQL Server Security: Fixed Server Roles - https://www.sqlservercentral.com/articles/sql-server-security-server-roles
• SQL Server Security: Pros and Cons of Application Roles - https://www.sqlservercentral.com/articles/sql-server-security-pros-and-cons-of-application-roles
• SQL Server Security: The db_executor Role - https://www.sqlservercentral.com/articles/sql-server-security-the-db_executor-role
• Using the Public Role to Manage Permissions - https://www.sqlservercentral.com/articles/using-the-public-role-to-manage-permissions