MSSQLTips

This post is related to this tip: Run The Same SQL Command Against All Databases

http://www.mssqltips.com/tip.asp?tip=1414

Thanks for your wonderful scripts!

You save my time and money!

Have a great day!

 

nice script ! thanks a bunch!

 Thanks so much. 

Tim Ford did a great job with this tip.

Thank you,
The MSSQLTips.com Team

You may need some "[" and "]" around the '?' to deal with dodgy database names.......

 as in:

EXEC sp_MSforeachdb 'USE [?] SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'

R

You are absolutely correct.  This is particularly important when dealing with spaces in database names.

--// Just another flavor to offer IF OBJECT_ID('tempdb..##TempDBInfo') IS NOT NULL BEGIN PRINT 'Previous ##TmpDBInfo found and Dropped.' DROP TABLE ##TempDBInfo END CREATE TABLE ##TempDBInfo(DBName nVarChar(255), FileName nvarchar(255), fileSize INT) EXEC sp_MSforeachdb 'USE [?] INSERT INTO ##TempDBInfo SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF' SELECT * FROM ##TempDBInfo --// DROP TABLE ##TempDBInfo

Hi All, 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.

 
EXEC sp_msforeachdb 'USE ?; if object_id(''sp_StoredProc1'') is not null begin print ''?'' GRANT EXECUTE ON [dbo].[sp_StoredProc1] TO public end'