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
Sheddy
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
Aleksandr Fedorenko
source
to share
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
Ben thul
source
to share