December 8, 2010 at 12:46 pm
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
December 8, 2010 at 1:13 pm
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.
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
December 8, 2010 at 10:19 pm
In addition to what Craig has posted, I don't even think Service Broker is availalbe in express.
December 9, 2010 at 12:15 am
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:
3013 ScheduledJob_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?
December 9, 2010 at 1:38 am
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:
3013 ScheduledJob_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.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply