Implicit Permissions Due to SQL Server Database Roles
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?
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:
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.
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.
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.
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.
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 sp_helprolemember 'db_datareader';
EXEC sp_helprolemember 'db_datawriter';
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';
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';
- Take the time to audit security and permissions on your servers.
- Read these related tips:
- Auditing SQL Server Permissions and Roles for the Server
- Auditing SQL Server User and Role Permissions for Databases
- Understanding Cross Database Ownership Chaining in SQL Server
- Understanding How A User Gets Database Access in SQL Server
- Understanding SQL Server fixed database roles
- Understanding SQL Server fixed server roles
About the author
View all my tips
Article Last Updated: 2010-11-17