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'