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,

RichAre 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.|||

Quote:

Originally Posted by rob313

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".|||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.