CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Sheddy
Sheddy

Reputation: 119

sp_MSforeachdb issues

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?

Upvotes: 3

Views: 910

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

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.

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 1

Related Questions