SQL Server Performance Forum – Threads Archive

entirely denying EXEC permission from a user

Is it possible to entirely deny EXEC permission from a user? ’Entirely’ in this questions means that a user shouldn’t be able to execute any stored procedure or function. I know I can explictly deny EXEC permission on every stored procedure and every function, but that’s not very convenient. I consulted BOL and I think that is is not possible, but I just want to make sure, since in ‘MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, Exam 70-228, Second Edition’ I found an example like this: DENY INSERT, UPDATE, DELETE TO Joe It doesn’t even compile, but its author must’ve had something in mind, haven’t they? <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> – Marek ‘chopeen’ Grzenkowicz ’You’re so cute when you’re frustrated.' — Interpol

It doesn’t compile? I just ran this deny exec on test1 to public and it works fine. But there’s no way to do this db-wide without explicitly doing it for every single one, which you say you don’t want to do. DENY does and should work, as documented in BOL. Tom Pullen
DBA, Oxfam GB

quote:Originally posted by thomas I just ran this deny exec on test1 to public and it works fine. But there’s no way to do this db-wide without explicitly doing it for every single one, which you say you don’t want to do.

Try to run:
deny exec to public (without ‘on test1’)

quote:Originally posted by thomas But there’s no way to do this db-wide without explicitly doing it for every single one, which you say you don’t want to do.

Just as I thought. There must be a mistake in the book. — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol

Yep, you need to have the object in there! Someone needs to do some proof reading. Tom Pullen
DBA, Oxfam GB

]]>