question

basit 1 avatar image
basit 1 asked

How Can we find user have Execute Right on SP ?

Hi, We have 1000s of SP in our Database how can we check which user have Execute right on that SP. is there any Query to find out which user have execute right on which SP. Thanks Basit
permissions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
You can use for example below query, but note that the list contains only direct rights associations. It doesn't take in mind if a principal is member of some role and the role has assigned execute permission etc (it means it doesn't show the effective permission). Also if an user is listed here and have an execute permission, it could be overridden by Deny permission of some parent group of database permission. Also it doesn't lists permissions which were granted on a schema. As if the execute permission is granted on the schema, then user have execute permissions to all the stored procedures in particular schema. The same relates to database level permissions. (you can easily alter the query to retrieve permission for database, schemas etc. For details take a look on MSDN related to the system tables mentioned in the query). So to extract the real permissions it is a more complex work. SELECT s.name AS SchemaName, o.name AS ObjectName, dp.name AS PrincipalName, dperm.type AS PermissionType, dperm.permission_name AS PermissionName, dperm.state AS PermissionState, dperm.state_desc AS PermissionStateDescription FROM sys.objects o INNER JOIN sys.schemas s on o.schema_id = s.schema_id INNER JOIN sys.database_permissions dperm ON o.object_id = dperm.major_id INNER JOIN sys.database_principals dp ON dperm.grantee_principal_id = dp.principal_id WHERE o.type IN ('P', 'PC') --P = SQL Stored Proce, PC - CLR Stored Proc AND dperm.class = 1 --object or column AND dperm.state IN ('G', 'W') AND dperm.type = 'EX'
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image basit 1 commented ·
But i didnot find the User name who have the Execute Right
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Ah, I forgot to include the Principal Name in the query.. Now it is fixed and also it returns the user name. :-)
0 Likes 0 ·
basit 1 avatar image basit 1 commented ·
Thanks paval for your help...
0 Likes 0 ·
sandhya avatar image sandhya commented ·
It is not returning user name. Can you please help me. i need user name/logins to be displayed near object name.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.