Re: xp_logevent and service broker queues

Great, sa as owner works. Many thanks ;-)

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im Newsbeitrag news:8BA868E9-B48D-4149-8632-D3C523EB4B1D@xxxxxxxxxxxxxxxx
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. ... This queue receives all messages and starts the stored procedure sp_AsyncReceive which is owned by the dbo. ... 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: 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)
  • Re: rda push when owner is not dbo.
    ... If I launch the sql query analyzewr and try to run the ... I'm pretty configdent that the owner name not being qualified is the ... > I pull the table with rda to a local table named task. ... > I also created a test table in the same database with owner dbo and I ...
    (microsoft.public.sqlserver.ce)
  • Re: Question about dropping owners permissions...
    ... When a member of the db_owner role creates an object, ... dbo, must the owner must be specified: ... >>> create databases themselves. ... Can anyone help me reach my end goal: Allowing developers to ...
    (microsoft.public.sqlserver.security)
  • Re: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)