Search code examples
sql-servert-sqlaudit

tsql for getting a user's permissions without impersonation


Is there a check I can make to see if a user has modify rights to any object on the server without running the query as that user?

I need to create a logon audit report that lists logon times for accounts that have insert/update access for something on the server(i.e. on any database on the server)

This also needs to not impact performance(if possible) or have the smallest impact.


Solution

  • From this article:

    select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
         sys.database_permissions.permission_name, 
         sys.database_permissions.state permission_state,
         sys.database_permissions.state_desc,
         state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
     from sys.database_permissions
     join sys.objects on sys.database_permissions.major_id = 
         sys.objects.object_id
     join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
     join sys.database_principals on sys.database_permissions.grantee_principal_id = 
         sys.database_principals.principal_id
     order by 1, 2, 3, 5
    

    You would want to filter by username. And keep in mind this shows only the explicitly defined permissions (and deny rules), not effective permissions.