3

As DB-Administrator I need to keep overview about my servers and databases in general - and from a security context about logins, users, db-roles and permissions in particular.

It is possible to write queries that deliver all relevant information to reduce effort required to lookup each and every user / permission manually within SSMS for example to find out the settings or to create kind of a documentation about it (that get's outdated the next day). Doing this I created a table having the following information torn out of my servers:

Servername | Datebasename | Username | Login | DatabaseRolename | Permission | PermissionState | SchemaName | ObjectName

This includes information about User-Permissions on object-level directly as well as through DB-Roles and also recursive DB-Role-in-DB-Role permissions inherited.

Instead of looking at this data or create a small application that allows more comfortable lookups / searches or filtering I would like to have it presented more graphical. I looked at some sampe js libraries that allow visualization. Something that may be appropriate is this samples:

http://vowl.visualdataweb.org/webvowl/

http://orgo.stolarsky.com/

My question is if anyone knows a way for visualization of this data? Maybe there is a solution for this everyone is using I did not yet stumble about.

1

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

  1. Direct grants to Database User on the Object
  2. Grants implied by membership in one or more Database Roles that has permissions on the Object
  3. 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
  4. Grants implied by Schema-level permissions
  5. Grants implied by Database-level permissions
  6. Grants implied by membership in one or more Fixed-Server Roles by Server Login associated with the Database User
  7. A DENY anywhere in the chain should supersede any GRANTs

Then you need to take into account:

(B) Windows Groups-based Logins

  1. 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 ;-).
  2. 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 DENY.
  3. Windows Logins can be associated with multiple Windows Groups, each Group having it's own Login, but no Login mapped to the Windows Login
  4. 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

  1. A Login/User having IMPERSONATE permission on another Login/User having more permissions than the original Login/User
  2. Any Stored Procedure, Function (excluding Inline TVFs), or Trigger created with the EXECUTE AS clause
  3. Ownership Chaining
  4. 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 securable_class of 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',
                          'PRIMARY_KEY_CONSTRAINT', 'SEQUENCE_OBJECT',
                          'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION',
                          'SQL_STORED_PROCEDURE', 'SQL_TABLE_VALUED_FUNCTION',
                          'SQL_TRIGGER', 'SYNONYM', 'UNIQUE_CONSTRAINT', 'USER_TABLE',
                          'VIEW');

-- 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_';

--PRINT @OuterSQL;

IF (OBJECT_ID(N'tempdb..#Permissions') IS NULL)
BEGIN
  -- DROP TABLE #Permissions;
  CREATE TABLE #Permissions
  (
    UserName sysname,
    EntityName sysname,
    SubentityName sysname,
    PermissionName NVARCHAR(100)
  );
END;

INSERT INTO #Permissions (UserName, EntityName, SubentityName, PermissionName)
  EXEC (@OuterSQL);

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 :-(.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.