A Permission Predicament

I recently encountered an unusual permissions issue with multiple databases. New databases were not including all of the permissions that were supposed to be set following database restores. At the time, I wasn’t sure if the permission was being granted and then revoked or not granted at all. I wanted a script I could run to definitively show that permissions did exist and also have proof for myself that, if permissions seemingly vanish later on while testing, I know they were present at one point in time.

Permission to Execute?

Let’s say we want to grant EXECUTE permissions on a schema to a new AccessTest user in the AdventureWorks2019 database. I went with this database since it has plenty of schemas which fit the scenario I was in. We can get a list of schemas by querying sys.schemas:

SELECT * FROM sys.schemas;
GO

Plenty of schemas to work with. We’ll pick the Person schema for this example. If we want to see existing EXECUTE permissions for AccessTest on the Person schema, we’ll need to join a few more tables.

I used this script, joining sys.database_principals and sys.database_permissions, to check for granted permissions:

SELECT pr.name AS 'Role_Name'
	,pe.Permission_Name
	,pe.State_Desc
	,s.name AS 'Schema_Name'
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
JOIN sys.schemas s ON s.schema_id = pe.major_id
WHERE pr.name = 'AccessTest'
	AND pe.permission_name = 'Execute'
	AND s.name = 'Person';
GO

I specified AccessTest for pr.name (database_principals name), EXECUTE for the pe.permission_name (database_permissions permission_name), and Person for the s.name (the schema name).

Nothing returned, which indicates our AccessTest user doesn’t have EXECUTE permissions on Person yet.

Permission Granted

Let’s grant EXECUTE access for our AccessTest user with the following statement:

GRANT EXECUTE ON SCHEMA :: Person TO AccessTest;
GO

We’ll check again with our SELECT from above and confirm our newly granted permission:

I can save this information for future reference in case I need a sanity check on the permissions that were in place.

Thanks for reading!