question

Akbar avatar image
Akbar asked

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-2008sqlstored-procedurespermissions
10 |1200 characters needed characters left characters exceeded

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

Tom Staab avatar image
Tom Staab answered

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 characters needed characters left characters exceeded

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

Kev Riley avatar image
Kev Riley answered

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 comments
10 |1200 characters needed characters left characters exceeded

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

@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 Likes 0 ·
@Kev Riley It's working now and i have used the script given by Tom and thanks for your answers as well.
0 Likes 0 ·
Jack Corbett avatar image
Jack Corbett answered

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 comment
10 |1200 characters needed characters left characters exceeded

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

@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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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