Execute Permission to Particular Stored Procedure or Function in All databases of a SQL Server 2008 Instance

Question

question

Akbar asked Jan 27, '10

Execute Permission to Particular Stored Procedure or Function in All databases of a SQL Server 2008 Instance

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.

sql-server-2008 sql stored-procedures permissions

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-27T13:49:35Z

Tom Staab answered Jan 27, '10

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'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-27T13:53:23Z

Kev Riley answered Jan 27, '10

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

another angle:

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.

run

EXEC sp_msforeachdb 'print ''?'';USE ?; GRANT EXECUTE ON dbo.sp_StoredProc1 TO UserOrRoleName'

into text and see which database gives you the error

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Akbar commented · Jan 27, 2010 at 02:11 PM

@Kev Riley I have modified the script and even it's showing the same error. But i have the sp_StroedProc1 in my database underneath the stored procedure section of sql server 2008. Any suggestions? DECLARE @grantExecute varchar(8000) select @grantExecute = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? GRANT EXECUTE ON [dbo].[sp_StoredProc1] TO web END' EXEC sp_MSforeachdb @grantExecute

0 ·

Akbar commented · Jan 27, 2010 at 03:12 PM

@Kev Riley It's working now and i have used the script given by Tom and thanks for your answers as well.

0 ·

Answer 3 · 2010-01-27T13:59:09Z

Jack Corbett answered Jan 27, '10

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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Akbar commented · Jan 27, 2010 at 02:20 PM

@Jack The below one is also not working . sys.sp_MSforeachdb @command1 = 'If ''?'' not in (''TempDb'', ''master'', ''model'', ''msdb'') Begin GRANT EXECUTE ON [dbo].[sp_StoredProc1] TO web End;'

0 ·

question