Tag Archives: how check sql server schema permissions

How to check SQL Server schema user permissions

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , ,

The script below will, providing the login you are using has adequate permissions, return the schema permissions a user has.

Commented out at the end of the script are examples of the types of permission you can assign, again providing the login you are using has adequate permissions.

SELECT ClassDescription
	,StateDescription
	,PermissionName
	,SchemaName
	,UserName
FROM (
	SELECT class_desc AS ClassDescription
		,state_desc AS StateDescription
		,permission_name AS PermissionName
		,SCHEMA_NAME(major_id) AS SchemaName
		,USER_NAME(grantee_principal_id) AS UserName
	FROM sys.database_permissions AS PERM
	JOIN sys.database_principals AS Prin ON PERM.major_ID = Prin.principal_id
		AND class_desc = 'SCHEMA'
	) AS schemaPermissions
WHERE 1=1 
/*Uncomment below to check permissions on a specific schema and/or specific user*/
--	AND SchemaName = 'dbo'
--	AND UserName = 'SomeGuy'
ORDER BY UserName ASC
,SchemaName ASC
GO



/*
--Grant schema permission examples
GRANT SELECT ON SCHEMA::dbo TO SomeGuy;
GRANT UPDATE ON SCHEMA::dbo TO SomeGuy;
GRANT ALTER ON SCHEMA::dbo TO SomeGuy;
GRANT DELETE ON SCHEMA::dbo TO SomeGuy;
*/