Grant Exec permissions to all SPROCS

[mtorpy]

I am trying to write a script to grant EXEC permissions on all non-system stored procedures on all DB's to a specific user 'QATest'

The below code will do this successfully but I need a way of writing it so it will run on ALL databases. Avoiding writing the same code for all db's on the system.

Any help?

---------------
declare @sql nvarchar(4000)
declare @db sysname ; set @db = 'Test32' --Change DB NAME
declare @u sysname ; set @u = QUOTENAME('QATest')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

PRINT 'Adding EXEC Stored Procedure permissions to ' + @u + ' on data base ' + @db

exec master.dbo.xp_execresultset @sql,@db

 

[vongrunt]

Have you tried sp_msforeachdb?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mtorpy]

OK Tried this but no luck as of yet is it possible?


EXEC sp_MSforeachdb
@command1 = 'IF ''?'' NOT IN(''master'', ''model'',''tempdb'',''msdb'')
IF EXISTS (select * from ?..sysusers where name = ''QATest'' and issqluser = 1)
begin
print ''Granting EXEC Permissions to QATest user in ?''

declare @sql nvarchar(4000)
set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + 'QATest' + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

exec ?.dbo.xp_execresultset @sql,?
end'

 

[mtorpy]

OK I GOT IT

EXEC sp_MSforeachdb
@command1 = 'IF ''?'' NOT IN(''master'', ''model'',''tempdb'',''msdb'')
begin
declare @u sysname
set @u = QUOTENAME(''QATest'')

declare @sql nvarchar(4000)
set @sql =''select ''''grant exec on '''' + QUOTENAME(ROUTINE_SCHEMA) + ''''.'''' +
QUOTENAME(ROUTINE_NAME) + '''' TO '' + @u + '''''' FROM INFORMATION_SCHEMA.ROUTINES '' +
''WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''''IsMSShipped'''') = 0''


PRINT ''Adding [EXEC] permissions to all nonsystem stored procedures to user '' + @u + '' on database '' + ''[?]''
exec master.dbo.xp_execresultset @sql,''?''

end'