Re: Tracing user & permissions (triggers and stored p)
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 20 Jan 2006 07:09:29 -0600
Please specify the version of SQL Server you are using. Assuming 2000,
there should be no difference in the Windows account used regardless of how
xp_cmdshell is run (proc vs. trigger executes proc). The process launched
from xp_cmdshell executes under the security context of the SQL Server
service account when run by a sysadmin role member. xp_cmdshell runs under
the configurable SQL Agent Proxy account for non-sysadmin users. The proxy
account can be configured from Enterprise Manager under Management-->SQL
Server Agent-->Properties-->Job System.
There are issues when running BCP from a trigger. A trigger always executes
in the context of a SQL Server transaction. Consequently, modified data
will be locked and cannot be accessed by the external BCP utility unless
NOLOCK is specified. Also, long-running transactions are bad for both
concurrency and performance.
I suggest you consider an alternative approach for your requirements. A
common practice is to insert the needed data into staging tables and
schedule a periodic SQL Agent job to generate the files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Guillem Codina" <Guillem Codina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9156ECAF-E52B-4044-859F-A693F08C6109@xxxxxxxxxxxxxxxx
> To whom might be able to help me:
>
> STORY:
> I work in a stevedor terminal in the Autonomous port of Barcelona.
> Every time operations on a vessel are closed (instruction from software)
> an
> sql Trigger executes a Stored Procedure passing it some variables.
> The Stored Procedure ends generating flat files which will then be
> captured
> by a Daemon who sends them by FTP to a client for later processing.
>
> The flat files are generated using the following command:
>
> Exec master.dbo.xp_cmdshell @cmd, no_output
>
> @cmd is a variable which will store a value like this:
>
> BCP "Select * From terminal.Temp_BCP" queryout
> "P:\\CONTXBUQ.A59562454.ESQ0817002I.20060120111821.X.EDIPLANO.N.msg.txt"-Sxxxxxxx
> -Usa -Pxxxxxxxxxx -c
>
> PROBLEM:
> The exec comand ends on error:
>
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]No existe el
> servidor SQL Server o se ha denegado el acceso al mismo.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Connect()).
>
> I am certain that parameters -S -U and -P are correct as the whole exec
> command.
>
> I guess that the key to the problem is "permissions". When I execute
> from
> QueryAnalyzer the permissions are the ones from the user who has logged
> into
> SQL or WINDOWS (depending on the security system used).
> Stored Procedure' s permissions must work different, specially when its
> execution comes from a Trigger. I would like to know how to check for the
> chain of permissions to see what user is really executing the xp_cmdshell.
> SQL Server xp's must have very restrictive policies since many of them can
> access server resources. Do you believe that the user executing the
> cmdshell
> is by default LocalSystem? If so, how can I change this user? are there
> any
> parameters for this purpose? what user could perform with no problems
> this
> cmdshell?
>
>
> Thanks a lot in advance.
>
.
- Follow-Ups:
- Re: Tracing user & permissions (triggers and stored p)
- From: Guillem Codina
- Re: Tracing user & permissions (triggers and stored p)
- Prev by Date: Re: Permission Evaluation
- Next by Date: CREATE TABLE permission denied in database
- Previous by thread: How to see jobs.
- Next by thread: Re: Tracing user & permissions (triggers and stored p)
- Index(es):
Relevant Pages
|
