T-SQL DENY EXECUTE

tags:

sql
tsql

views:

answers:

T-SQL DENY EXECUTE

what is the t-sql command/syntax to deny execute permissions to EVERYONE except the dbo on stored procedures? I want to grant execute to roles and users will then inherit rights based on the roles they belong to. as such i want new users to be denied execute on all the stored procedures i create.

thanks

sorry - i should mention i did this for a database already. i'm trying to create the db again but seem to have forgotten the code on how i did it.

i thought it was sonmethign like "deny execute to all"?

Try this link:

http://msdn.microsoft.com/en-us/library/ms188338%28SQL.90%29.aspx

Himadri 2009-11-12 10:28:38

If you explicitly deny them permissions to these objects, adding them to a role which can execute them will not work - DENY has precedence. By default no user has permissions to execute a stored procedure - the permission must be explicitly granted to either the user or the role they belong to. Do you really need more than just the default?

CodeByMoonlight 2009-11-12 10:54:47

the grant will be given to the roles.i just need to have the fault to be denied execute.everything is via stored procedures and security is based on access via rolescurrently, if i drop a stored procedure and re-create it(instead of alter), even as dbo, i cant execute it unless i assign it to a role and grant execute via the role.on the new db, it doesnt seem to be workingt he same.

azad 2009-11-12 11:22:24

An explicit DENY will block them from executing the stored procedure regardless of the permissions of any role they belong to (unless they are also a db_owner). The default (ie no permissions set) will not allow them to execute the stored procedures.

CodeByMoonlight 2009-11-12 11:39:18

ansaurus

tags:

views:

answers:

T-SQL DENY EXECUTE

related questions