Grant Exec from inside a SP
-
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?
-
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
-
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?
-
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?
-
Thanks for sharing the information , will need to test it further to confirm
-Sat
Cheer Satish
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply