Leave a comment ?

11 Comments.

  1. HI,

    Is it possible to run xp_cmdshell on remote server – a linked server? I want to run WMIC Volume command to get the SAN details from remote sql servers. So, do we have this possibility?

    Thanks!

  2. Very good question! If you want to execute some kind of Windows command – example: “dir c” from SQL to a remote server destination, you can’t do this by using xp_cmdshell. xp_cmdshell lets you connect remotely through sqlcmd to another SQL instance and execute only SQL statements. Windows commands (example: exec xp_cmdshell ‘dir c’) will output the results from the local computer, not the remote one. In order to collect disk information from other remote computers you can use psexec tool. You can find here the details. .

  3. Hi,

    User executes stored proc from web application and stored procedure then calls xp_cmdshell command. And, users are having read only permission. Now users are unable to run this as their group not part of sysadmin group.

    Can we deviate the stored procedure to use service account to run xp_cmdshell instead of user group a/c using SSIS?

  4. The stored procedure cannot be deviated by itself. The user that executes the SP used in your web application has the credentials needed to access the SP and run it. But if you want that user to be able to execute xp_cmdshell, and not having to give sysadmin you can do this by using a proxy windows account instead.

  5. I have to send this script off to an admin. Just before I do though, is it possible to impersonate on a development server that may not be connected to a domain controller?

  6. Ms. IT, 🙂
    I had a question for you regarding sqlcmd.
    If I had a ‘read only’ privileged user (desgined for odbc connection through an application) , is there a way to limit the user’s ability to connect through the cmd line?

    I understand that the user login will only fetch the data from the objects I allowed, but I wanted to know if there is someway I could sqlcmd based on privilege.
    I would like to mention here that on our instance, sqlcmd was enabled for a purpose and so I need to keep it on.
    thanks.
    Siva

  7. Yes you can revoke the permission to execute xp_cmdshell ‘sqlcmd…’ from whatever user you want, even public role if this is much easier for you.

    Here is what you need to do:

    use master
    go
    REVOKE ALL ON xp_cmdshell FROM [Test_user_name]
    go

    Error when trying to execute xp_cmdshell stored procedure, being connected as [test_user_name]:

    Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
    The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

  8. please see my reply down here.

  9. Hi,

    I have two servers (A&B) loaded with SQL server. I have connected to the remote SQL server on B from server A. Can I rename a file on server B using xp_cmdshell over the DB connection?

    I am getting the below error message

    Msg 121, Level 20, State 0, Line 0
    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)

    Thank you,
    Joe

  10. no, you can’t send windows commands from one server to another using linked servers.
    xp_cmdshell, as I mentioned in my post works remotely only for SQL related commands, meaning everyhting taht changes, alters, modifies sql data wil work. Windows commands (ex: “dir”, “rename”) also work but for the currently connected server.

    to launch a remote windows task you need to use a third party tool, called psexec. I used it and I remember solving this issue.

  11. Aleksandar Marinov

    Hi all,

    In fact it is possible to execute OS commands like dir d:\ on a remote SQL server:

    See at the end of
    this thread.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>