Is there are way to grant Execute permission to a particular Stored procedure or Function in all database of SQL Server 2008 Instance?
The below one use to Grant Execute permission to any procedure.
EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName'
But when trying the below one I'm getting error
EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE ON [?].[dbo].[sp_StoredProc1] TO UserOrRoleName' Error Message: Cannot find the object 'sp_StoredProc1', because it does not exist or you do not have permission.
I have the 'sp_StroedProc1' exist in all my database in the sql server 2008 instance. Any help will be highly appreciated.
Answer by Tom Staab ·
I suggest you run this to verify the existence of the stored procedure in all databases.
EXEC sp_msforeachdb 'USE ?; SELECT DB_NAME(), OBJECT_ID(''dbo.sp_StoredProc1'')'
Scan the results for any row (database) with NULL in the second column.
EDIT: I don't know why it shows in the Object Explorer if it's not really there (unless you just need to refresh the display), but this should solve your problem:
EXEC sp_msforeachdb 'USE ?; IF OBJECT_ID(''dbo.sp_StoredProc1'') IS NOT NULL GRANT EXECUTE ON dbo.sp_StoredProc1 TO UserOrRoleName'
Answer by Kev Riley ·
sp_msforeachdb will actually cycle through all the databases on the server including master, msdb, model, tempdb - and I doubt that your stored procedure is in those databases
If you can determine which database is failing, maybe you (the user that is running the grant statement) don't have the permission to GRANT.
EXEC sp_msforeachdb 'print ''?'';USE ?; GRANT EXECUTE ON dbo.sp_StoredProc1 TO UserOrRoleName'
into text and see which database gives you the error
Answer by Jack Corbett ·
I'm going to guess that you are getting the error when it runs in the system databases as I am assuming you are not putting the procedure in the system databases. So you could do something like this:
sys.sp_MSforeachdb @command1 = 'If ''?'' not in (''TempDb'', ''master'', ''model'', ''msdb'') Begin [place code here] End;'