Getting Permissions In SQL Server

In my rare moments of spare time, I’ve started to work on an application to show exactly what permissions each account has on different objects.  I’m focusing primarily on CRUD operations on tables, but want to expand this to a holistic view of an entire instance.  At present, there is nothing like a quality interface within SQL Server Management Studio which lets you do just that.  Yes, you can go to the Securables page for a Login and get explicit properties, but there are several problems with this:

1. You can get Explicit grants, but if the login is a member of a server role, you don’t see those results like you would expect.  For example, as a sysadmin, I can impersonate other logins implicitly, meaning that the Explicit tab (by design) leaves out important information.
2. If you use Active Directory groups, you can’t get Effective properties on those groups.  In other words, if I am a member of an AD group which has rights to impersonate a login, even the Effective tab won’t show me the truth.  Instead, it will just give me an error:  “Cannot execute as the server principal because the principal “%s” does not exist, this type of principal cannot be impersonated, or you do not have permission.”  Yes, this does work if you add a login for a particular Windows account, but if part of your security posture involves using AD groups to provide access, you’re out of luck.
3. Even if you don’t use AD groups, it’s time-consuming to get the actual permissions for a number of different users.  Going through step-by-step and extracting these permissions for users is a mess:  you can’t copy and paste from the Effective permissions tab (at least in SQL Server 2008) and you have to go through a lot of mouse-clicks to find out exactly what a login/user can do.  Then, on top of that, when you get to the databases themselves, you have to remember that sysadmins don’t need user mappings, making it that much more difficult to track down actual permissions.

The SQL Server permissions model is very rich, but unfortunately, that richness leads to a great deal of complexity.  My long-term goal with this project is to fill in that gap with a free, open tool.  I’m aiming this primarily at dealing with requests from regulators and auditors, but it should also be of some value to front-line DBAs.

I tried several methods of tracing out permissions, but it quickly turns into a mess when you have to deal with server, database, and application roles.  It turns out that the easiest way to get permissions is from the fn_my_permissions function (2005 and later).  This gives you effective permissions instead of explicit permissions, but that’s usually what I want.  Unfortunately, it only gets your permissions, meaning that you can’t directly get permissions for another login unless you impersonate (meaning you need to be a sysadmin to get the full functionality out of this).

--Get effective permissions at the instance level
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

--Get effective permissions at the database level
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

--Get effective permissions for objects:  tables, views, stored procedures, functions, triggers, etc.
select distinct
	st.name,
	st.type,
	st.type_desc,
	mp.permission_name
from
	sys.all_objects st
	cross apply sys.fn_my_permissions(st.name, 'OBJECT') mp

This will probably be the backbone of my solution, with a (relatively…for me at least) pretty interface and some decent reporting capabilities. The downside is that this is slow: on a non-trivial database, the final query could take 10-15 seconds per person, returning tens of thousands of rows (because you get one row per permission per object). This is the type of thing you would run in a batch process, meaning that my app would need to be Powershell-friendly.