Tuesday, November 18, 2008

Batch permission change for MSSQL stored procs

I recently ran into a batch of existing stored procs that needed additional execute permissions. After some digging on the web I was able to put together a simple script to make the changes. Since I'll likely need it again in the future, I decided to throw it out here.

Assuming all of the stored procs contain the string 'MyApp' in the name, and all need execute permissions added for 'myuser', the following will make the necessary change

  declare @SQL varchar(8000)
  select @SQL = isnull(@SQL, '') + 
    'grant execute on [' + 
    routine_schema + '].[' + 
    routine_name + '] to [myuser];'
  from information_schema.routines 
  where SPECIFIC_NAME like '%MyApp%' 
  exec (@SQL )