Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: How to executed extended stored procedures as non-sysadmin users????

    Hi All,

    I have a scenario related to SQL 2005 Security, I have created database "db1" as "sa" user.

    Steps followed

    1.Created a "dummy" login
    2.Created a "dummy" user,mapped to "dummy" login for "db1" database and made database owner.
    3.logged in as "dummy" login and created a stored procedure which contains EXEC xp_cmdshell Statement and while executing the stored procedure, it is throwing below error.

    Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
    The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.


    4. Next, what i done is, i have turned on xp_cmdshell feature and
    created a proxy account using Windows Authentication Account.
    5. I Again tried to connect to "dummy" login and tried to execute the stored procedure and now it worked.

    Now my Question is, what does the below statement do.

    EXEC sp_xp_cmdshell_proxy_account 'domain\username', 'domain_user_password'

    What affect is there if i created this new account.
    I know this would create a Proxy Credential but i am wondering how would the "dummy" user can able to utilize this Proxy Account internally because externally/explicitly am not giving/executing any

    command to make use of this newly created proxy account.

    Can anyone please elaborate on how internally this Proxy Account is able to be utilized by the user "dummy" when he is getting logged in.

    Am just curious about what is happening internally?

    In other words, just want to know how this proxy account is made available to "dummy" user when he is getting logged in.

    Commands Used to replicate the scenario.

    Step1 : Create the database as "sa" user

    use master
    go
    CREATE DATABASE db1
    go

    Step2 : Create a login
    -- create a login "dummy" as "sa" user
    USE [master]
    GO
    CREATE LOGIN [dummy] WITH PASSWORD=N'dummy', DEFAULT_DATABASE=[db1], CHECK_EXPIRATION=OFF,

    CHECK_POLICY=OFF
    GO

    Step3 : Create a User in "DB1" database and make him database owner.
    USE [db1]
    GO
    CREATE USER [dummy] FOR LOGIN [dummy]
    GO
    USE [db1]
    GO
    EXEC sp_addrolemember N'db_owner', N'dummy'
    GO

    Step4 : login as "dummy" and create a stored procedure which uses xp_cmdshell and when you try to

    execute the stored procedure.

    It will throw an error.


    CREATE PROC USP_TEST
    AS
    BEGIN
    DECLARE @STR VARCHAR(100)
    SET @STR = 'GUEST'
    PRINT @STR

    EXEC sys.xp_cmdshell 'dir c:\*.*'

    END

    EXEC USP_TEST
    /*
    GUEST
    Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
    The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the

    '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

    */

    Step5 : Enable the xp_cmdshell feature as "sa" user


    EXECUTE sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE sp_configure 'xp_cmdshell', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE sp_configure 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE
    GO

    EXEC sys.xp_cmdshell 'dir c:\*.*'



    Step6 : Now login as Windows Authentication user and try to create a
    proxy account


    EXEC sp_xp_cmdshell_proxy_account 'GSPSTRAIL\Administrator', 'mychlocallogin'


    Step7: login "dummy" user and again try to execute the stored procedure.
    EXEC USP_TEST

    Step8: To drop the proxy account, execute the below peice of code.
    /*
    -- To drop a Proxy Account
    -- login as Windows Authentication and execute the below command
    /*
    EXEC sp_xp_cmdshell_proxy_account NULL
    */


    Thanks in Advance.

  2. #2
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    You need to grant the access to the user before you are using the account...

    Check the following article...

    Proxy Accounts in SQL Server – Database Journal

  3. #3
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    You can also use WITH EXECUTE AS option...

    Check example in the following blog and syntax in BOL...
    Tibor Karaszi : Xp_cmdshell and permissions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •