469,917 Members | 1,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Deny user roles from a Stored Procedure.

Hello,

I am trying to deny a user from processing a query, however the user still seems to be able to see the procedure.

I have executed the following command on both the 'Public' and specific user name.

DENY EXECUTE ON [OBJECT_NAME] TO [PUBLIC]

However when i run the command 'sp_helprotect OBJECT_NAME' afterwards it shows that deny is set on 'Public', yet the user can still run the procedure from a web report... I have tried restarting IIS and this also does nothing, any ideas?

Thanks,

Rich
Sep 10 '07 #1
3 2405
rob313
16
Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.
Sep 10 '07 #2
ck9663
2,878 Expert 2GB
Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.
the user might be a "dbo".
Sep 11 '07 #3
rob313
16
If the user is dbo then SQL Server does not check any other permissions (grant or deny), so that would be why you are seeing this behavior. You will need to remove the user from dbo and grant that account the needed permissions or role.
Sep 11 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Markus Palme | last post: by
3 posts views Thread by =?Utf-8?B?Q2hhcmxlc0E=?= | last post: by
3 posts views Thread by =?Utf-8?B?Um9nZXIgTWFydGlu?= | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.