February 27, 2014 at 9:32 am
is there a way to grant execute permission on SPs/Functions once , so that you don't have to do it each time a new object or user is created? For some reason i have to do it a lot.
February 27, 2014 at 10:58 am
You can use this..
GRANT EXECUTE ON SCHEMA::dbo TO Name;
--
SQLBuddy
February 28, 2014 at 6:52 am
I wrote this routine to be used only in the DEV environment and scheduled it as a job. It will grant execute for functions and sprocs based on the naming convention. We use naming conventions here, so it's easy. You'll probably need to modify accordingly.
The job runs every 2 minutes so the developers don't have to wait too long to test their code.
Note: You'll need to replace the role name [myrolename] between the brackets with the role you are assigning the permissions to.
declare @routineName sysname
declare @dataType sysname
declare syscursor cursor for
select routine_schema + '.' + routine_name, data_type
from INFORMATION_SCHEMA.ROUTINES
where (routine_name like 'spr_%' or routine_name like 'fn_%')
and
(
(routine_name not like 'spr_MAINT%')
and (routine_name not like 'fn_MAINT%')
)
open syscursor
fetch next from syscursor into @routineName, @dataType
while @@fetch_status = 0
begin
execute('grant execute on ' + @routineName + ' [myrolename]')
fetch next from syscursor into @routineName, @dataType
end
fetch next from syscursor into @routineName, @dataType
close syscursor
deallocate syscursor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply