This 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.

They are:

• 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

  database roles.

• 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.

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

time being.