Subscribe to this blog
Briefcase
Print
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.
No comments.