Tag Archives: permission

How to: Check Stored Procedure Permission for Specific User

The script below will provide you a list of all store procedures in particular database that owned by the specific user/login.

SELECT
	[name] AS [Store Procedures],
	[permission_name] AS [Permission]
FROM
	sys.objects obj
INNER JOIN
	sys.database_permissions dp ON dp.major_id = obj.object_id
WHERE
	obj.[type] = 'P' -- stored procedure
	AND dp.permission_name = 'EXECUTE'
	AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT
	AND dp.grantee_principal_id =
	(SELECT principal_id
	 FROM sys.database_principals
	 WHERE [name] = '[LOGIN_NAME]')

How to: Check Database User Permission

The following scripts was shared by Pradeep Adiga in his blog to check the permission granted/denied for the databases users. It is very useful when you need to check whether the user has permission for those stored procedures in SQL Server databases.

SELECT
    USER_NAME(dppriper.grantee_principal_id) AS [UserName],
    dppri.type_desc AS principal_type_desc,
    dppriper.class_desc,
    OBJECT_NAME(dppriper.major_id) AS object_name,
    dppriper.permission_name,
    dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id

References: