Post reply

GRANT EXECUTE returns 'mssqlsystemresource' does not exist

  • June 28, 2011 at 6:53 am

    #389851

    Why isn't this working?

    USE [Master]

    GO

    CREATE LOGIN [test] WITH PASSWORD=N'[test]', DEFAULT_DATABASE=master

    GO

    CREATE USER [test] FOR LOGIN [test]

    GO

    GRANT EXECUTE ON xp_cmdshell TO [test]

    GO

    DROP USER [test]

    DROP LOGIN [test]

    GO

    Executed by sa, the GRANT statement returns the error:

    Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.

    I have it also tried with another 'non sysadmin' SQL-user. Same error.

    Tnx.

  • June 28, 2011 at 9:29 am

    #1345267

    Is xp_CmdShell enabled on the instance? Have you setup the proxy?

    If I may ask, what's the goal of granting exec permissions to xp_CmdShell? Maybe there is a cleaner way to do what you're trying to do.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 28, 2011 at 9:35 am

    #1345275

    Here's an article discussing the steps referenced by opc.three

    http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 28, 2011 at 9:41 am

    #1345281

    SQLRNNR (6/28/2011)

    Here's an article discussing the steps referenced by opc.three

    http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

    Thanks Jason, I will keep this one to pass along, at least until Jeff publishes his article on how to manage xp_CmdShell securely.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 28, 2011 at 9:46 am

    #1345286

    opc.three (6/28/2011)

    SQLRNNR (6/28/2011)

    Here's an article discussing the steps referenced by opc.three

    http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

    Thanks Jason, I will keep this one to pass along, at least until Jeff publishes his article on how to manage xp_CmdShell securely.

    I have a feeling it may be an article on several ways to do it via other means and not use xp_cmdshell.;-)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 28, 2011 at 9:54 am

    #1345290

    What version of SQL Server are you using?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 29, 2011 at 6:12 am

    #1345846

    A lot of questions, and here are my answers:

    - xp_cmdshell is enabled

    - SQL server version is 2008 (forum topic = 2008)

    - I have the error with version 2008 and 2008 R2

    - I have tried the script from http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx and when I execute 'GRANT EXECUTE ON xp_cmdshell TO JohnDoe', I get the same error !!!

    - I have setup a proxy account for SQLserver Agent, and this works fine.

    - The goal is to set up a temporarly account which can copy files from server A to server B, for migration purposes.

    I have a workaround with the SQL Agent proxy account, but I was wondering why I am getting the error. Am I the only one with this error, is there something wrong with my installations, or what is gioing on.

    Eric.

  • June 29, 2011 at 9:03 am

    #1345982

    aarded (6/29/2011)

    ...

    - I have setup a proxy account for SQLserver Agent, and this works fine.

    ...

    Not a SQL Agent proxy, the xp_CmdShell proxy.

    -- remove proxy

    EXEC sys.sp_xp_cmdshell_proxy_account NULL

    GO

    -- create proxy (shows up in Object Explorer under /Server/Security/Credentials)

    EXEC sys.sp_xp_cmdshell_proxy_account 'domani\user', 'pwd'

    GO

    Please see section starting with "xp_cmdshell Proxy Account" here: http://technet.microsoft.com/en-us/library/ms175046(SQL.110).aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 30, 2011 at 9:22 am

    #1346618

    I kow!

    But I mean that it works with SQL server agent and a proxy account.

    I use this as a workaround.

    The problem is: I can't set the 'GRANT EXECUTE' permission.

    This returns the error:

    Executed by sa, the GRANT statement returns the error:

    Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly

  • June 30, 2011 at 10:11 am

    #1346642

    aarded (6/30/2011)

    I kow!

    But I mean that it works with SQL server agent and a proxy account.

    I use this as a workaround.

    The problem is: I can't set the 'GRANT EXECUTE' permission.

    This returns the error:

    Executed by sa, the GRANT statement returns the error:

    Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly

    Is there a different user you can connect to the db as? A different user that has sa access. It seems that maybe somebody might have hardened security on that server.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 30, 2011 at 10:33 am

    #1346653

    SQLRNNR (6/30/2011)

    aarded (6/30/2011)

    I kow!

    But I mean that it works with SQL server agent and a proxy account.

    I use this as a workaround.

    The problem is: I can't set the 'GRANT EXECUTE' permission.

    This returns the error:

    Executed by sa, the GRANT statement returns the error:

    Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly

    Is there a different user you can connect to the db as? A different user that has sa access. It seems that maybe somebody might have hardened security on that server.

    Agreed, it does not add up.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 4, 2011 at 8:01 am

    #1347867

    Yes it is possible to connect with other users.

    There many users, admins and others, windos and sql, which can connect.

    And with a proxy account, it works with SQL agent.

    So I think this can't be a server problem.

  • October 13, 2011 at 9:36 pm

    #1394434

    SQLRNNR (6/28/2011)

    opc.three (6/28/2011)

    SQLRNNR (6/28/2011)

    Here's an article discussing the steps referenced by opc.three

    http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

    Thanks Jason, I will keep this one to pass along, at least until Jeff publishes his article on how to manage xp_CmdShell securely.

    I have a feeling it may be an article on several ways to do it via other means and not use xp_cmdshell.;-)

    Quite the contrary, actually. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 14, 2011 at 12:33 am

    #1394457

    Executed by sa, the GRANT statement returns the error:

    It works perfectly fine on my system.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply