Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2009-04-28 : 05:30:53
|
| I have a stored procedure that queries a linked server, if i run the SP manually (EXEC usp_storedprocname) it runs OK and updates the tables, but if i try and run it from a schedule it fails with the following message:-"Invalid authorization specification"Any ideas on how i can get around this as i think sql server is trying to accees the remote server under the wrong profile. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-04-28 : 05:36:58
|
| You either need to run your SQL Server Agent account under a domain user with permissions or use EXECUTE AS <user> at the top of the procedure.http://msdn.microsoft.com/en-us/library/ms188354.aspx |
 |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-04-28 : 05:41:40
|
| Thanks, i'll take a look at that, i thought it might be something to do with using sp_addlinkedsrvlogin and setting the lod in details up in there but wasn't sure how to use that. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-04-28 : 06:36:55
|
| It shouldn't be, but I suppose it could cause an issue. From your explanation, I would say that SQL Agent doesn't have enough rights (you've probably got it set to local service account).You can try setting the linked server to make the connection using a certain login if you want. Just open it in Management Studio and set it there (the be made using this security context setting). |
|
|
|
|
|
|
Problem with scheduling Stored Procedure