Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Implicit Permissions Due to SQL Server Database Roles


By:   |   Last Updated: 2010-11-17   |   Comments (1)   |   Related Tips: 1 | 2 | More > 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


Last Updated: 2010-11-17


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, November 17, 2010 - 10:03:39 PM - Roel Back To Top

Hi Brian,

Good day.

May I ask, is there a way to grant only INSERT, UPDATE and exclude DELETE with db_datawritter role? Please show the way how to do it, am new with MS SQL Server 2008.

Thank you in advance for your time.

Roel


Learn more about SQL Server tools