Re: xp_logevent and service broker queues

Maybe somebody can give me a hint how to call 'sysadmin' functions under the dbo user or how to change permissions for calling these functions in a stored procedure which is started by a service broker queue.

With EXECUTE AS OWNER 'dbo', the database owner of your user database needs to be 'sa' in order for the security context to be 'sa' in master too. Also, see http://www.sommarskog.se/grantperm.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Marcus Müller" <marcusmueller@xxxxxx> wrote in message news:442C6DC7-B20E-4843-AD22-F499A8622A15@xxxxxxxxxxxxxxxx
Hi,

I got stuck in the SQL Server 2005 security dependencies and can't find out. Here's my problem:

I am using the service broker for running asynchronous stored procedures and have created a queue with

CREATE QUEUE AsyncTargetQueue WITH STATUS = ON, RETENTION = OFF,
ACTIVATION(STATUS = ON ,
PROCEDURE_NAME = sp_AsyncReceive,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER)

This queue receives all messages and starts the stored procedure sp_AsyncReceive which is owned by the dbo. The dbo has a login name who is member of the sysadmin role (sysadmin is required for running the xp_logevent procedure).

In the sp_AsyncReceive I am calling xp_logevent as user dbo with

EXEC xp_logevent 60000, @evtlogmsg, error

However, the call fails with the following error message in the event log:

'The EXECUTE permission was denied on the object 'xp_logevent', database 'mssqlsystemresource', schema 'sys'.'

Maybe somebody can give me a hint how to call 'sysadmin' functions under the dbo user or how to change permissions for calling these functions in a stored procedure which is started by a service broker queue.

Many thanks in advance,

Marcus

.

Relevant Pages

  • Re: xp_logevent and service broker queues
    ... With EXECUTE AS OWNER 'dbo', the database owner of your user database needs to be 'sa' in order for the security context to be 'sa' in master too. ... Maybe somebody can give me a hint how to call 'sysadmin' functions under the dbo user or how to change permissions for calling these functions in a stored procedure which is started by a service broker queue. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL2005 FT error adding table column
    ... sysconstraints dbo view ... ad_banners dbo user table ... PK_ad_banners dbo primary key cns ...
    (microsoft.public.sqlserver.fulltext)
  • Re: public role question
    ... If 'select user' is returning DBO, it means you are connecting AS DBO, and ... testing the permissions the new user has. ... Are you saying that the permissions to create the stored procedure are ... Dan D. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Disappearing
    ... Make sure that the "Record Source Qualifier" is set to dbo; ... Sylvain Lafontaine, ing. ... Did you use dbo. ... stored procedure up into several smaller ones, or is there a better way ...
    (microsoft.public.access.reports)
  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)