I do not believe that this is truly possible, at least not in any truly accurate and meaningful way. The problem is, even taking into account the level of complexity that you have already identified (emphasis added):
information about User-Permissions on object-level directly as well as through DB-Roles and also recursive DB-Role-in-DB-Role permissions inherited.
permissons are still quite a bit more complicated and involve several more layers.
The various layers of permissions that I am aware of are (the first 3 you already mentioned):
(A) Base permissions
- Direct grants to Database User on the Object
- Grants implied by membership in one or more Database Roles that has permissions on the Object
- Grants implied by membership in one or more Database Roles that is in turn a member of one or more Database Roles, across any number of levels of Database Role membership where at least one Database Role has permissions on the Object
- Grants implied by Schema-level permissions
- Grants implied by Database-level permissions
- Grants implied by membership in one or more Fixed-Server Roles by Server Login associated with the Database User
DENY anywhere in the chain should supersede any
Then you need to take into account:
(B) Windows Groups-based Logins
- You can verify if a Login is a member of a particular Windows Group using the IS_MEMBER built-in function, but there is no way to determine what Windows Groups exist to be a member of (well, at least not without using SQLCLR ;-).
- When using Logins based on Windows Groups, permissions are additive across all Group-based Logins and directly mapped Logins, but can be negated by a
- Windows Logins can be associated with multiple Windows Groups, each Group having it's own Login, but no Login mapped to the Windows Login
- Windows Logins can be associated with one or more Windows Groups and also having a Login mapped to the Windows Login
AND THEN you need to take into account the effects of:
(C) Permissions granted at run-time
- A Login/User having
IMPERSONATE permission on another Login/User having more permissions than the original Login/User
- Any Stored Procedure, Function (excluding Inline TVFs), or Trigger created with the
EXECUTE AS clause
- Ownership Chaining
- Module Signing (i.e. granting permissions to code -- Assembly, Stored Procedure, Function (excluding Inline TVFs), or Trigger -- using an Asymmetric Key or Certificate)
Using the sys.fn_my_permissions function you can loop through the various objects in a particular Database and get a list of effective permissions for the current User. And if you want the permissions of another User, then you have to use
EXECUTE AS USER = 'user_name'; and then run that loop, and then execute
REVERT;. You probably only need to check for a
OBJECT. So you could try something along the lines of the following:
SET NOCOUNT ON;
DECLARE @InnerSQL NVARCHAR(MAX) = N'';
SELECT @InnerSQL += N'SELECT SESSION_USER, * FROM fn_my_permissions(N''' + ss.[name]
+ N'.' + so.[name] + ''', ''OBJECT'');' + NCHAR(0x0D) + NCHAR(0x0A)
FROM sys.objects so
INNER JOIN sys.schemas ss
ON ss.[schema_id] = so.[schema_id]
WHERE so.[is_ms_shipped] = 0
AND so.[type_desc] IN ('AGGREGATE_FUNCTION', 'CHECK_CONSTRAINT',
'CLR_SCALAR_FUNCTION', 'CLR_STORED_PROCEDURE', 'CLR_TABLE_VALUED_FUNCTION',
'CLR_TRIGGER', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT',
'SQL_TRIGGER', 'SYNONYM', 'UNIQUE_CONSTRAINT', 'USER_TABLE',
-- PRINT @InnerSQL;
DECLARE @OuterSQL NVARCHAR(MAX) = N'----------------------------------'
+ NCHAR(0x0D) + NCHAR(0x0A);
SELECT @OuterSQL += N'EXECUTE AS USER = ''' + dp.[name] + ''';'
+ NCHAR(0x0D) + NCHAR(0x0A) + NCHAR(0x0D) + NCHAR(0x0A)
+ @InnerSQL + NCHAR(0x0D) + NCHAR(0x0A)
+ N'REVERT;'+ NCHAR(0x0D) + NCHAR(0x0A)
+ N'----------------------------------' + NCHAR(0x0D) + NCHAR(0x0A)
FROM sys.database_principals dp
WHERE dp.[type_desc] IN ('SQL_USER', 'WINDOWS_USER')
AND dp.[principal_id] > 4
AND LEFT(dp.[name], 5) <> N'##MS_'
AND LEFT(dp.[name], 3) <> N'MS_';
IF (OBJECT_ID(N'tempdb..#Permissions') IS NULL)
-- DROP TABLE #Permissions;
CREATE TABLE #Permissions
INSERT INTO #Permissions (UserName, EntityName, SubentityName, PermissionName)
SELECT * FROM #Permissions;
HOWEVER, you can only get info from section A above, and not even all of that given that you cannot get permissions for Windows Groups-based Users as you cannot use
EXECUTE AS on those.
AND, you cannot take into account anything from section B without actually logging in as each individual Login.
AND, you cannot determine any net-effect from anything from section C without cross-referencing everything previously determined and figuring out some means of determining "effective permissions" on Users that cannot be impersonated (e.g. Certificate-based, Asymmetric Key-based, Windows Groups-based, etc), hence they were not discovered by the query shown above.
So, um: No :-(.