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 |
|
returnofthemack
Starting Member
16 Posts |
 Posted - 2003-07-08 : 22:17:55
|
| I've reviewed the 4 forum posts on this issue and still do not know if there is a way around this - so I'll pose this scenario to you all:I have a stored procedure on Server A (SQL2K), which queries tables on Server B (SQL7) through the use of a linked server (created on Server A). This works fine when run via Query Analyzer. The problem arises when I'm attempting to run this stored procedure through a job in SQL Server. I want to run this job as "user A", but it gives me the "Remote access not allowed for Windows NT user activated by SETUSER" error message. I tried to set the owner to "SA" and the individual steps to run as "user A", but that doesn't work. I don't explicitly use any "set user" commands, so I'm thinking that SQL Server is using the command on its own.1. Is there a way around this such that I can run this stored procedure with a user other than SA (I'm not even sure that SA will even work)? 2. Does SQL Server use the "set user" command on its own? If so, is there a way around that?3. Other info?Thanks in advance! |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-09 : 03:55:14
|
| Does user a have a login account and permissions on the SQL 7 box?What are the security settings on your linked server? Could you set it up so that user a is mapped to a different user on the SQL 7 box? Does that fix your problem?-------Moo. |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-07-09 : 13:57:46
|
| SQL Agent issues a SETUSER 'guest' WITH NO RESET when running a job owned by a non sysadmin to lower it's permissions (As the SQL Agent is considerably more privileged than a simple login, often running in the security context of the local system account, it must ensure that, when a T-SQL job is submitted to it, it can't be abused). Have a look atPRB: SQL Server Agent Job Fails When the Job Uses a Linked Server and the Job Owner Is Not a System Administrator[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;811031[/url]HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
|
|
|
Remote access not allowed for Windows NT user acti