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

Akbar gravatar image

Akbar
55 3 3 3

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

3 answers: sort oldest

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

Tom Staab gravatar image

Tom Staab
5.8k 6 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, 2010 at 02:06 PM Akbar

@Tom

Thanks a ton. It's working like charm now. Thanks again for the help.
Jan 27, 2010 at 03:11 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

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

@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

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

Jack Corbett gravatar image

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, 2010 at 02:20 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1834
x713
x407
x85

asked: Jan 27, 2010 at 01:26 PM

Seen: 5906 times

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