sp_MSforeachdb issues

Question

I'm trying to assign VIEW DEFINITION access for stored procedures in an environment at work for developers and run the query below as Result to Text then copy the output and run as Result to Grid:

select 'GRANT VIEW DEFINITION ON ' + quotename(specific_schema) 
+ '.' + quotename(specific_name)
+ ' TO ' + 'TestAcct'
  from INFORMATION_SCHEMA.routines
where routine_type = 'PROCEDURE'

The problem I have is that I have to do this against each and every database on multiple servers. I have tried to run it with 'sp_MSforeachdb' but when I setup the query it doesn't like my select statement. Any ideas on how to format the above query for all DBs so that I can copy the output and run it against a server instead of each individual DB?

Answer 1

EXEC sp_MSforeachdb 
'SELECT ''GRANT VIEW DEFINITION ON '' + quotename(''?'') + ''.'' + quotename(specific_schema) 
+ ''.'' + quotename(specific_name)
+ '' TO '' + ''TestAcct'' AS ''?''
FROM ?.INFORMATION_SCHEMA.routines
WHERE routine_type = ''PROCEDURE'''

Answer 2

If you're just going to let your test account view the definition of whatever, why not:

grant view definition to [TestAccount]

That way, it stays current when you create new objects within the database. Simpler solution that works better, in my opinion.