CODEWITHSUNDEEP
sqlsql-serverpermissions
sam strider
sam strider

Reputation: 13

SQL - Finding out what Stored Procs a user doesn't have execute permissions for.

Good Afternoon,

so I have been tasked with trying to check the user permissions for a specific user, across 413 stored procedures. Mainly for the ones that it DOESNT have access too.

I currently have : - 

select name, 
HAS_PERMS_BY_NAME(name, 'OBJECT', 'EXECUTE') as has_execute
from sys.procedures
where HAS_PERMS_BY_NAME(name, 'OBJECT', 'EXECUTE') = 0;

but for some reason, this doesn't work, and the table displays nothing. Yet I know that it doesn't have execute permissions on some tables, I can see it!

Now, i'm just a lowly apprentice and have not been doing SQL for particularly long, so any guidance would be greatly appreciated.

Thanks

Upvotes: 0

Views: 231

Answers (1)

websch01ar
websch01ar

Reputation: 2123

As an alternative to the approach you are taking, the script below will return all procedures for a given database that a specified user has Execute permissions on. It can easily be adjusted for other types (e.g., views or tables) as well as other specified permission levels.

USE [Database]
GO

DECLARE @UserToCheck varchar(200) = 'UserToCheck'

SELECT DISTINCT  
    O.Name as ProcedureName,
    IIF(P.Name IS NULL, 0, 1) AS HasExplicitExecutePermission,
    P.Name as SysUser , 
    P.permission_name As Permission
FROM sys.objects O
LEFT JOIN (
    SELECT A.major_id, B.Name, A.permission_name
    FROM sys.database_permissions A
    JOIN sys.database_principals B ON  A.grantee_principal_id = B.principal_id
    WHERE 
        B.Name = @UserToCheck AND 
        A.permission_name = 'EXECUTE' AND
        A.state IN ('G', 'W')
) P ON P.major_id = O.object_id
WHERE 
    o.type = 'P'  --Stored Proc
Order By HasExplicitExecutePermission Asc, ProcedureName

Upvotes: 2

Related Questions