x

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.

more ▼

asked Jan 27, 2010 at 01:26 PM in Default

avatar image

Akbar
55 3 3 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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'
more ▼

answered Jan 27, 2010 at 01:49 PM

avatar image

Tom Staab ♦
14.5k 7 14 19

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 27, 2010 at 01:59 PM

avatar image

Jack Corbett
1.1k 3 4 7

@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, 2010 at 02:20 PM Akbar
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 27, 2010 at 01:53 PM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

@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, 2010 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, 2010 at 03:12 PM Akbar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091
x1014
x457
x110

asked: Jan 27, 2010 at 01:26 PM

Seen: 7539 times

Last Updated: Jan 27, 2010 at 09:13 PM

Copyright 2016 Redgate Software. Privacy Policy