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

Question

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.

Answer 1

0

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'

answered Jan 27 '10 at 01:49 PM

Tom Staab
5.8k ● 5 ● 8 ● 10

@Tom Staab

It's showing NULL for the SP but it's present in the all the database under my stored procedure section of sql server 2008. Any suggestions?

Jan 27 '10 at 02:06 PM Akbar

@Tom

Thanks a ton. It's working like charm now. Thanks again for the help.

Jan 27 '10 at 03:11 PM Akbar

(comments are locked)

Answer 2

0

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

answered Jan 27 '10 at 01:59 PM

Jack Corbett
1.1k ● 2 ● 2 ● 3

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

Jan 27 '10 at 02:20 PM Akbar

(comments are locked)

Answer 3

0

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

answered Jan 27 '10 at 01:53 PM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

@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

Jan 27 '10 at 02:11 PM Akbar

@Kev Riley

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

Jan 27 '10 at 03:12 PM Akbar

(comments are locked)

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

Follow this question

Related Questions