LLI Wiki — My Digital Knowledge Base

SQL Server permission queries

List database permissions, effective permissions of a user, and the principals that can start a SQL Server Agent job.

T-SQL queries that list the permissions granted in a SQL Server database, and that check which principals can start a SQL Server Agent job.

Permissions granted in the database

SELECT class_desc , CASE WHEN class = 0 THEN DB_NAME() WHEN class = 1 THEN OBJECT_NAME(major_id) WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable] , USER_NAME(grantee_principal_id) [User] , permission_name , state_desc
FROM sys.database_permissions

Effective permissions of the current user

The function fn_my_permissions returns the permissions that can be checked, depending on the scope passed as parameter.

select * from fn_my_permissions(NULL, NULL);
select * from fn_my_permissions(NULL, 'database');
select * from fn_my_permissions('dbo', 'schema');
select * from fn_my_permissions('dbo.test', 'object');
Call Scope
fn_my_permissions(NULL, NULL) Server
fn_my_permissions(NULL, 'database') Current database
fn_my_permissions('dbo', 'schema') dbo schema
fn_my_permissions('dbo.test', 'object') Table dbo.test

Who can start a SQL Server Agent job

The following query lists the principals that hold a permission on the sp_start_job procedure in the msdb database.

USE MSDB
GO
SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC
FROM MSDB.SYS.DATABASE_PERMISSIONS DP
JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID
JOIN MSDB.SYS.DATABASE_PRINCIPALS PR
ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'
Mastodon LLIMastodon SocialFosstodon