Re: Cross-database execution permissions with certificates and sch

Craig Thomas (CraigThomas@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
In the article Uri references
(http://www.sommarskog.se/grantperm.html#ownershipchaining) there is a
section that describes the perils of using "EXECUTE AS OWNER",
particularly when the owner has plenty of privileges. Yet in every
reference I read about Service Broker, EXECUTE AS OWNER is the way to
achieve the cross-database permissions needed for an "activated" stored
procedure.

You cannot accuse for knowing too much about Service Broker, but I have
at least read Roger's book.

I don't really see anything being different for Service Broker than
for anything else. You do need an EXECUTE AS in the queue activation,
but that's a different thing from having WITH EXECUTE AS in a stored
procedure.

Since Service Broker runs as a system process, to execute an activation
procedure, it has to be someone, and you use the EXECUTE AS clause in
ALTER/CREATE QUEUE to specify that.

I would suggest that best practice is to use a dedicated user, so that
when you look at audit trails, blocking scenarios etc quickly can see
that what comes from Service Broker.

But as I indicated: I have worked very little with Service Broker myself.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations
    ... I'm only going to address the Service Broker issues (and some EXECUTE AS ... > I've installed SQL Server 2005 Developer Edition on a couple of boxes. ... > Service accounts to a specific, non-built-in, local user. ...
    (microsoft.public.sqlserver.security)
  • RE: Permissions required to select from sys.services?
    ... pasted below is encapsulated in a stored procedure. ... Execute As Owner. ... I think EXECUTE AS has implications ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Give Permissions to user to execute Jobs
    ... How do I allow specific users access to specific jobs on the sql server? ... need it so that two people can execute the same job, ... one user is the owner on the job. ...
    (microsoft.public.sqlserver.security)
  • sql server access violation from VB applicaton
    ... accesses a sql server 2000 stored proc that he created ... stored proc but this proc has DBO as the owner then the ... execute his own stored proc successfully. ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)