Grant Exec from inside a SP

Question

Post reply

Grant Exec from inside a SP

Paul Mu

SSCertifiable

Points: 5547
More actions
April 30, 2009 at 5:23 am

#220694

Hi,
I have an interesting situation executing the following test script:
CREATE PROC spTesting
WITH EXECUTE AS OWNER
AS
GRANT EXEC ON OBEJCT::sp_name TO role_name;
GO
When I execute this proc (with owner as sysadmin), the following error is raised:
Msg 2115, Level 16, State 1, ...
Server level event notifications are disabled as the database MSDB does not exist.
However, if I commented out the WITH EXECUTE AS OWNER clause, it works fine!
What could be the sense of the error message, or am I missing something here?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 1

Not able to reproduce the message you are getting script is executing fine in my environment.

Can you check if you can perform basic queries on MSDB databases ?

-:-)

Cheer Satish 🙂

Paul Mu SSCertifiable Points: 5547 More actions · Answer 2

I am sysadmin on the particular test server. Nothing unusual in the msdb database.

I tried it on a secondary server and it works fine there. I do use Event Notifications, but I can't see how that could be related to the WITH EXECUTE AS clause?

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

As a follow up: I've dropped the server-level event notification and it now works. Recreated a database-level event notification and it still works. Could this be a bug in SQL Server 2005?

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 4

Thanks for sharing the information , will need to test it further to confirm

-Sat 🙂

Cheer Satish 🙂