Re: VB 6, SQL Server 2000, xp_cmdshell

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 22:53:47 +0200

Inline below...

> Second thing: it seems that I know less than I thought about
> users/logins/accounts and I feel like complete idiot.

No need to. No-one of us were born with this knowledge.

> ...but it's better to ask and get some answers (<snip>)
> , than to keep my mouth shut and feel like I feel at
> the moment, many times after.

Agree.

> Server role of that login is system administrator. It means it's
> sysadmin, right?

Not sure what you mean by "System Administrator"? Can you elaborate? I'm referring to the SQL Server
server role named "sysadmin".

You need to understand what the SQL Server service role "sysadmin" is. Read about server roles in
Books Online.

Then make sure you understand what a service account in Windows mean. This is the user context that
the service is using.

And finally: If the login who owns a job has sysadmin privileges in SQL Server, then xp_cmdshell
will be executed using the SQL Server service account.

If the login who owns the job isn't sysadmin, then the proxy account is used. You must in these
cases configure a proxy account, or you will get an error message.

Also, note that SQL Server does *not* perform any type of impersonation. I.e., xp_cmdshell will
*not* execute in the user context of the windows account who is logged on to SQL Server (unless it
happens to be the same as any of the two mentioned above, of course). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hundredhouses" <hundredhouses@yahoo.com> wrote in message 
news:d29a6a54.0409280713.40d0d40c@posting.google.com...
> First of all thank you Tibor, thank you Peter.
> Second thing: it seems that I know less than I thought about
> users/logins/accounts and I feel like complete idiot. It's very
> embarrassing but it's better to ask and get some answers (and feel
> like idiot once), than to keep my mouth shut and feel like I feel at
> the moment, many times after.
>
>
>> >> When the xp_cmdshell runs, it runs using the NT User ID
>> >> used in starting the SQL Server, so it doesn't matter
>> >> which log in you use
>> >
>> >Unless the login who executes xp_cmdshell isn't sysadmin.
>>  If the login isn't sysadmin, then the
>> >proxy account is used. The proxy account is defined in
>>  EM, right-click SQL Server Agent.
>> >
>
> Server role of that login is system administrator. It means it's
> sysadmin, right?
>
>> >> When the xp_cmdshell runs, it runs using the NT User ID
>> >> used in starting the SQL Server, so it doesn't matter
>> >> which log in you use (except of course for rights to
>> >> executing the xp_cmdshell).
>> >>
>> >> I would look at what user id is been used to start the
>> >> service, then ensure that that id has access rights to
>>  the
>> >> directory your trying to access. Remember that Server
>> >> directories have there own access rights  independant of
>> >> SQL.
>> >>
>
> I've found that user who starts the service didn't have permissions to
> select/insert/update/delete some tables in a database and it was
> supposed to. I don't know how important it is but I gave those
> permissions and it still doesn't work. The same was with the stored
> procedure I made.
>
> Tell me something guys. I've realized that user who starts the service
> is important one. Is this always or just when I use xp_cmdshell? I
> still don't realize why it has to have access rights to the directory
> I'm trying to access(in my case my database, right? Not the web
> page?).
> Let me ask you this way. When I make a job and the owner of the job is
> sa for example. Different user starts the service. When the job starts
> it starts using user who is owner of the job or the one who starts the
> services or that depends of the fact if xp_cmdshell is involved or
> not?
>
>
> You wrote :Remember that Server directories have there own access
> rights independant of SQL.
>
> Can you please explaine this sentence.
>
> Thank's one more time to both of you.
>
> Marko

Relevant Pages

  • Re: permissions required for executing CDOSys stored procedures
    ... he is by default member of the sysadmin server ... role on the SQL Server database unless steps are taken to prevent that. ... sysadmin and who has not been granted specific execute permissions on the ... it is possible to GRANT EXECUTE ON sp_OACreate TO ...
    (microsoft.public.sqlserver.security)
  • Re: Sharepoint index problems in SQL Server.
    ... The Administrator account is a sysadmin and have all the access to all the ... > database is master and language US_English? ... >> I've set back the account localsystem for both SQL Server and Microsoft ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Move or Setup of SQL Database toa Remote Server Fails
    ... as well as have SysAdmin privileges on the DB. ... Registered type 501ST MASTER for 501ST SCCM1DB\master ... Could not connect SQL Server 'master' db. ... The SCCM Primary Site Computer Account and the User account I am using ...
    (microsoft.public.sms.setup)
  • Re: cannot acees two databases as owner
    ... member of the built-in group Administrators, is not sysadmin. ... Administrator of SQL Server. ...
    (microsoft.public.sqlserver.setup)
  • Re: order of columns
    ... If you are sysadmin, you will be able to set an option that will allow you to perform direct ... Tibor Karaszi, SQL Server MVP ... >>> CREATE TABLE tblRichTest1( ...
    (microsoft.public.sqlserver.programming)
Quantcast