June 4, 2007 at 1:40 pm
We are running SqlServer 2005. For my current project, there is a group defined to which we need to Grant Execute permission for all Select stored procs. I have been manually adding the group by right clicking the stored proc and then choosing Properties, Permissions, etc.
I'm thinking there has to be a way to script this and make it faster and less error prone if I forget one.
A. Can I script this process to pass in the StoredProc name and then have it add the group for me?
B. Can I write a script like A that only adds the group if it does not already exist?
C. Can I write a script that returns this information for every stored proc in the database?
Sorry if these are really obvious questions. Thanks in advance for any ideas and/or suggestions!
June 5, 2007 at 1:24 am
how do you determine which procs are "select" procs? if they are named in a certain way you could do this:
select 'GRANT EXEC ON ' + quotename(name) + ' TO mygroup' from sys.objects where name like '%select%' and type='P'
this assumes that each of your procs that you want to grant exec to mygroup on has the word "select" somewhere in its name.
Just run this query, and execute the result.
---------------------------------------
elsasoft.org
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply