ServiceBroker: Access to another db

Question

ServiceBroker: Access to another db

teutales

Say Hey Kid

Points: 671
More actions
December 8, 2010 at 12:46 pm

#228309

Hello,
I'm new to ServiceBroker. I want to rebuilt the indexes on an express server every night with the service broker. So my target is one servicebroker which manages all my databases. But it seams like there are not enough rights when executing the script by the broker.
Do you have some tips? 🙂
Thanx
Stefan

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

teutales (12/8/2010)
Hello,
I'm new to ServiceBroker. I want to rebuilt the indexes on an express server every night with the service broker. So my target is one servicebroker which manages all my databases. But it seams like there are not enough rights when executing the script by the broker.
Do you have some tips? 🙂
Thanx
Stefan

:blink: Wha? The Service Broker won't rebuild indexes, the DBCC command to do so will. Set it up in your SQL Agent to do it on schedule with the SQL Agent's login having enough rights to do so.

Service Broker is for asynchronous data transfers, not server maintenance.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

John Rowan SSC Guru Points: 56440 More actions · Answer 2

In addition to what Craig has posted, I don't even think Service Broker is availalbe in express.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

teutales Say Hey Kid Points: 671 More actions · Answer 3

So you are right - the agent is the correct task to do this. In SQL Express I do not have an agent - but the service broker is available. I built a job which is fired every night... That works fine for all statements fired in the same db.

When I backup a foreign db I get:

3013ScheduledJob_Run: BACKUP DATABASE is terminating abnormally.

I'm no master in mssql server rights - I think the Service Broker is executed by dbo. It's possible to GRANT rights to dbo user to access other db's?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

teutales (12/9/2010)
So you are right - the agent is the correct task to do this. In SQL Express I do not have an agent - but the service broker is available. I built a job which is fired every night... That works fine for all statements fired in the same db.
When I backup a foreign db I get:
3013ScheduledJob_Run: BACKUP DATABASE is terminating abnormally.
I'm no master in mssql server rights - I think the Service Broker is executed by dbo. It's possible to GRANT rights to dbo user to access other db's?

To grant one process the ability to run stuff from another, you use cross-database ownership chaining. It allows DBO in one db to look like DBO in another, and can be considered a security risk.

I'd have to go do some research before I can remember what account Broker runs under. I believe it runs under the account actually listed in the broker services. Go to my computer - rt-click - manage to find that service.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA