none
Understading SQL SERVER permissions. RRS feed

  • Question

  • Hi everyone!

      I wrote the following SQL to query about granted permissions on production databases and look at security...

    SELECT -- lists the permissions explicitly granted or denied to server principals.
        principal.principal_id,
        LEFT(LOWER(principal.name), 50) COLLATE DATABASE_DEFAULT AS principal_name,
        LEFT(LOWER(principal.type_desc), 20) COLLATE DATABASE_DEFAULT AS principal_type,
        LEFT(LOWER(permission.state_desc), 10) COLLATE DATABASE_DEFAULT AS permission_type,
        LEFT(LOWER(permission.permission_name), 30) COLLATE DATABASE_DEFAULT AS permission_name,
        LEFT(LOWER(permission.class_desc), 30) COLLATE DATABASE_DEFAULT AS permission_class,
        CASE
            WHEN permission.class_desc = 'OBJECT_OR_COLUMN'
                THEN LEFT(LOWER(s.name + '.' + o.name), 50)
            WHEN permission.class_desc = 'SCHEMA'
                THEN LEFT(LOWER(s.name + '.' + o.name), 50)
            ELSE
                '-'
        END COLLATE DATABASE_DEFAULT AS object_name
    FROM sys.server_principals AS principal
        JOIN sys.server_permissions AS permission ON principal.principal_id = permission.grantee_principal_id
        LEFT OUTER JOIN sys.objects AS o ON permission.major_id = o.object_id  
        LEFT OUTER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
    WHERE principal.type IN ('S', 'U', 'G', 'R')

    UNION ALL

    SELECT -- lists the permissions explicitly granted or denied to database principals.
        principal.principal_id,
        LEFT(LOWER(principal.name), 50) COLLATE DATABASE_DEFAULT AS principal_name,
        LEFT(LOWER(principal.type_desc), 20) COLLATE DATABASE_DEFAULT AS principal_type,
        LEFT(LOWER(permission.state_desc), 10) COLLATE DATABASE_DEFAULT AS permission_type,
        LEFT(LOWER(permission.permission_name), 30) COLLATE DATABASE_DEFAULT AS permission_name,
        LEFT(LOWER(permission.class_desc), 30) COLLATE DATABASE_DEFAULT AS permission_class,
        CASE
            WHEN permission.class_desc = 'OBJECT_OR_COLUMN'
                THEN LEFT(LOWER(s.name + '.' + o.name), 50)
            WHEN permission.class_desc = 'SCHEMA'
                THEN LEFT(LOWER(s.name + '.' + o.name), 50)
            ELSE
                '-'
        END COLLATE DATABASE_DEFAULT AS object_name
    FROM sys.database_principals AS principal
        JOIN sys.database_permissions AS permission ON principal.principal_id = permission.grantee_principal_id
        LEFT OUTER JOIN sys.objects AS o ON permission.major_id = o.object_id  
        LEFT OUTER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
    WHERE principal.type IN ('S', 'U', 'G', 'R')
    ORDER BY principal_name;
    GO

      but it returns some information that I may need some help to understand:

    1. From the following screenshot, may I presume the principal name 'public', a database role, received someday on the past a select permission to an object that does not exist anymore on the user database? That's the reason for the NULL value on the object name column or maybe the query above has something wrong?

    Hope I was clear enough.


    Doria

    Tuesday, July 18, 2017 9:26 PM

Answers

All replies