Clever Geek Handbook

Sp_MSforeachdb issues

I am trying to assign VIEW DEFINITION access to stored procedures in a development environment and execute the query below as "Result for Text", then copy the result and execute it as "Result for Grid":

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

The problem is that I have to do this against every database on multiple servers. I tried to run it with "sp_MSforeachdb" but when I customize the query I don'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 on the server instead of each individual DB?

+3


source to share


2 answers 


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'''


+1


source


If you just want your test account to look at the definition of anything, why not:

grant view definition to [TestAccount]


Thus, it remains relevant when creating new objects in the database. A simplified solution that works better in my opinion.

+1


source






More articles:


All Articles