How to verify execute permission on stored procedure using query

_{Monday, July 23, 2018 10:39 AM}

Hi

I can grant an EXECUTE permission on my database with the Query

use [SP_Config]
GO
GRANT EXECUTE ON [dbo].[proc_putObjectTVP] TO [WSS_Content_Application_Pools]
GO

Problem:

How can I verify this setting with another query?

This is what ive googled myself to so far:

Use [SP_Config]
select name, 
    has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute
from sys.procedures
where name = 'proc_putObjectTVP';

But running this seems to give value 1 even if i remove the EXECUTE permission manually on the database. >_<

any hints&/ortips are welcome:-)

brgs

Bjørn

_{Monday, July 23, 2018 10:53 AM ✅Answered}

Hello,

That's already the right way to check the effective permissions. Is that role may be member of the db_owner role? Then role member can do anything in the database without explicit granted permissions.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Monday, July 23, 2018 11:02 AM}

Ah you were right Olaf, user was a member of the db_owner. Thanks!