Implicit Permissions Due to SQL Server Database Roles

By: K. Brian Kelley | Comments (1) | Related: 1 | 2 | > Security

Problem

I have audited for permissions on my databases because users seem to be accessing the tables, but I don't see permissions which give them such rights. I've gone through every Windows group that has access to my SQL Server and into the database, but with no success. How are the users accessing these tables?

Solution

Likely the issue is due to implied permissions. One thing to check are the roles within the database which give implicit permissions to objects. The ones we're concerned about are:

db_owner
db_datareader
db_datawriter

We're also concerned with the following at the server level:

members of the sysadmin server role

If a user is a member of any of these roles (or has the CONTROL permission) then we won't see any explicit permissions within the database. And they'll have the ability to access the database objects, even though we can't find the permissions either using sp_helprotect (SQL Server 2000) or by querying sys.database_permissions. So let's look at what each can do.

db_owner

Members of the db_owner fixed database role can do anything within the database unless explicity blocked by a DENY. So they can issue SELECT, INSERT, UPDATE, and DELETE against every table and view and EXECUTE against every stored procedure and scalar function. They can create objects in any schema and drop objects in any schema. They can add and drop users, add and drop roles, and determine who is a member of each role. They can also manage permissions at any level within the database. Therefore, membership in the db_owner role should be strictly controlled.

A similar permission that is not a role is if a particular user owns the database. In that case they come into the database as the dbo user. That user can do anything in that database and nothing blocks them. Therefore, databases should be owned by logins which don't belong to particular users.

db_datareader

Members of the db_datareader role have the ability to issue a SELECT query against tables and views unless explicitly blocked by a DENY. Again, there's nothing in the permission tables which give an indication such permissions are granted. Therefore, you must audit for the membership of this role to determine who has the ability to retrieve information from the tables and views in a given database.

db_datawriter

Like db_datareader, db_datawriter also gives implicit permissions which do not appear in the security tables. However, instead of granting SELECT permissions, it grants INSERT, UPDATE, and DELETE permissions implicitly unless there is an explicit DENY in place. And like with db_datareader, you must audit for the membership of this role to determine who has the ability to modify information in the tables and views for a database.

sysadmin

Members of the sysadmin role map into a database as the dbo user (even if they don't explicitly own the database). Therefore, they don't undergo security checks, just like the dbo user doesn't, and they can do anything within a database. So if you're trying to determine why a particular user has access to a database, you need to check this role at the server level, too.

Auditing Database Roles

The trick to auditing the members of a particular database role is to use the system stored procedure sp_helprolemember. So, for all of my databases, I do the following to determine who has implicit rights to access database objects:

EXEC sys.sp_helprolemember'db_owner';
GO

EXEC sp_helprolemember 'db_datareader';
GO

EXEC sp_helprolemember 'db_datawriter';
GO

Auditing the SysAdmin Server Role

In order to audit the sysadmin server role, you'll need to use a different system stored procedure: sp_helpsrvrolemember (note the srv embedded in the name):

EXEC sp_helpsrvrolemember 'sysadmin';
GO

Auditing the owner of the database

This is not that difficult, but it does require a quick join between two tables. Since the older, deprecated security tables still work in 2005/2008, I'll just use them for all versions of SQL Server, from 7.0 and up:

SELECT l.name AS 'login'
FROM sysusers u
INNER JOIN master..syslogins l
ON u.sid = l.sid
WHERE u.name = 'dbo';
GO

Next Steps

Take the time to audit security and permissions on your servers.
Read these related tips:

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips