http://www.sqlservercentral.com/blogs/brian_kelley/2006/03/14/rights-within-the-database/ Printed 2014/11/28 11:26AM
Rights within the database
2006/03/14This is a follow-on to my post What are the actual rights?
First and foremost, I want to include membership in the fixed database
roles. That's fairly easy to pull based on individual users. The
db_datareader and db_datawriter roles are naturally of great interest
because they grant explicit access to all tables and views. However, if
I want to get all the permissions, I need to look deeper. Within an
individual database several areas have to be looked at for permissions.
Keep in mind that none of this takes into account ownership chaining.
Unfortunately, to be able to do that means parsing functions, stored
procedures, and views to determine what objects are being accessed and
then determining ownership. If we have objects with the same name owned
by different owners, we have to evaluate what object is being
referenced. There's also the potential of referencing objects in other
databases. At this point I'm not interested in going down that path.
I'm interested in implicit permissions due to fixed database roles or
database ownership and explicit permissions granted to database
security principals. So that is the scope I'll be looking at for the
- Database owner - Whoever is the owner of
the database has the ability to completely bypass permissions.
Therefore, I need to take this into account, especially if a database
belongs to a particular login and not a generic one like sa
- Members of the db_datareader and db_datawriter fixed database roles
- I've mentioned this above, but just producing a report saying "Said
user is a member of the role" may not be sufficient to convey how much
access a particular user has. Therefore, if we produce a report saying
said user has SELECT access, we need to take into account these fixed
- Members of the db_denydatareader and db_denydatwriter fixed database roles
- Like the previous point, these roles block access. And since DENY
trumps an explicit grant, this may mean a user doesn't have the access
other roles would grant. We have to take this into account when
reporting what access a user actually has.
- Permissions assigned against individual users - This is a no-brainer. We need to know what a particular user account has been assigned explicitly.
- Permissions assigned to database roles
- Best practices says to create user-defined database roles and assign
permissions to them. Therefore, we can't just go with the permissions
defined for the user. By the way, we do need to consider both
user-defined and the public fixed database role as permissions can be
assigned directly to public. Other fixed database roles aren't a
concern because we can't assign permissions directly to them.
- Users as members of database roles - In order to map permissions properly, we need to keep track of what database roles a user is a member of.
- Database roles nested within database roles
- Since database roles can be nested, we need to keep track of what
roles are members of what other roles. This will also pose some
complexity when we look at interrogating Windows logins which happen to
be security groups.