Monday, March 26, 2012

exec permision for sql user

I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
Thanks.There is no builtin role for executing stored procedures. You could make the user db_owner, but that will bring in a load of other permissions that you probably would not want the user to have. In order to solve the immediate problem, you could run this query, to generate all the grant statements:

select 'grant execute on ' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + ' to role'
from INFORMATION_SCHEMA.ROUTINES|||hey, thanks for the information.
what does it mean 'SPECIFIC_SCHEMA ' and specific_name' in the above query.|||Those are the two columns you need from the INFORMATION_SCHEMA.Routines view. You should be able to cut and paste the code as is. The only thing you have to change is the name of the user or role you are granting the permissions to.

No comments:

Post a Comment