none
execute as 'sa' .Not yet solved. please help..

    Question

  • --Logged in as sa
    
    
    
    use EKTData
    
    create Proc ExecuteSql(@StrSql nVarchar(4000)) with execute as 'sa'
    
    AS
    
    Begin
    
    EXECUTE (@StrSql)
    
    End
    
    
    
    --This is ok
    
    Select * from sys.objects
    
    
    
    --This is not working. error The server principal "sa" is not able to access the database "EKTData" under the current security context.
    
    ExecuteSql 'Select * from sys.objects'
    
    
    • Edited by sqlblr Friday, December 18, 2009 11:23 AM
    Friday, December 18, 2009 6:54 AM

All replies

  • Hello,

    First check that user is properly mapped with Master database and then check that sa have a sysadmin role. Then try sys.object


    Thanks

    Thanks

    Regards,
    RM Thirunavukkarasu MCP, MCITP
    http://www.thiruna.blog.com
    Friday, December 18, 2009 7:08 AM
  • EXECUTE AS requires a database user_name in this context (not login_name).

    Check out the following:

    USE Northwind; 
    GO 
    
    CREATE PROC ExecuteSql 
               @StrSql NVARCHAR(4000) 
    WITH EXECUTE AS 'dbo' 
    AS 
      BEGIN 
        EXECUTE( @StrSql) 
      END 
    GO 
    
    -- Execute sproc
    EXEC ExecuteSql   'Select * from sys.objects' 
    GO 
    
    DROP PROC ExecuteSql 
    GO 
    Let us know if works.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, December 18, 2009 7:39 AM
  • Hi SQLUSA,

       Thanks for the reply. Infact I want a low privilaged user to run an xp_cmdshell command on the server and the user himself cannot be included in the sys_admin fixed role due to sercurity reasons. So  I thought of creating a wrapper procedure which will be executed in the security context of 'sa'.
    Will it serve the purpose if I change it to execute as 'dbo' ?  But I found that even I (logged in as 'sa') cannot run the procedure. Any work around?

    --Logged in as sa
    Create Proc ExecuteSql(@StrSql nVarchar(4000)) with execute as 'dbo'
    AS
    Begin
    EXECUTE (@StrSql)
    End
    
    --This is working
    xp_cmdshell 'DIR C:\'
    
    --This is not working. error: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
    ExecuteSql 'xp_cmdshell ''DIR C:\'''
    I have enabled  xp_cmdshell by using sp_cofigure and reconfigure
    Friday, December 18, 2009 9:10 AM
  • Any idea guys ??
    Friday, December 18, 2009 11:25 AM
  • Can you mark the "answer"(s) in this thread and open a new thread for for the new question?

     Thanks.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, December 18, 2009 12:23 PM
  • There is 'not' an answer yet in this thread.

    Sounds like you have enabled using xp_cmdshell, but have not provided permissions to any user.

    EXECUTESQL will not work in this context.

    And EXECUTE in not the best option. Using EXECUTE leaves your server vulnerable to SQL Injection attacks.

    I suggest using sp_executesql, and expecially using parameters with sp_executesql. Without using parameters, you are creating a extemely large security 'hole' in your server.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Saturday, December 19, 2009 6:44 PM
  • EXECUTESQL will not work in this context.

    And EXECUTE in not the best option. Using EXECUTE leaves your server vulnerable to SQL Injection attacks.

    I suggest using sp_executesql, and expecially using parameters with sp_executesql. Without using parameters, you are creating a extemely large security 'hole' in your server.


    Extremely important point from Arnie.

    Through xp_cmdshell not only DIR can be performed but also DEL(ETE) file(s).

    'dbo' is a database user as such in one or more database or application roles.

    xp_cmdshell, an OS utility,  requires CONTROL SERVER permission.

    Probably you can achieve your goal using sp_xp_cmdshell_proxy_account . However as Arnie pointed out you would be creating a security risk.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Sunday, December 20, 2009 1:14 AM
  • Thanks  Arnie & Kalman.

    But that did not answer my question. My problem is not with the security even though I know that the example I sited is
    Vulnerable to SQL injections. I just sited it as an example and I am not going to put it as it is.

    OK. See the following scenario. What I want to do all is to enable the user 'qauser' to run the procedure CopySourceFiles without assigning the user sys_admin privilege. All what I want to know is that Is it possible or not?

     

    --Logged in as sa
    
    CREATE LOGIN QALogin WITH PASSWORD = 'India*498';
    CREATE USER QAUser FOR LOGIN QALogin WITH DEFAULT_SCHEMA = dbo
    
    Create Proc CopySourceFiles with execute as 'dbo'
    AS
    Begin
    	execute xp_cmdshell 'xcopy "\\mssfs01\FtpArea\*.txt" "E:\Sourcefiles"'
    End
    
    GRANT IMPERSONATE ON USER::sa TO [QAUser] 
    GRANT EXECUTE ON CopySourceFiles To [QAUser] 
    
    
    --Login in as QALogin
    
    CopySourceFiles
    --Error. The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
    
     
    Monday, December 21, 2009 5:04 AM
  • Check out the following thread related to using sp_xp_cmdshell_proxy_account:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/40d67d22-28ac-44ab-b7d2-0eec25f873a6
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Monday, December 21, 2009 6:24 AM
  • Thanks Kalman.

      That tells me how to run xp_cmdshell for a user who is not a member of  sys_admin fixed group. Fine. But note that it does not make use of the user context switcing facility. Infact  this is only a single case and I have a lots of Procedures like below

    1) ProCopyScourceFile 
           --Uses xp_cmdshell utility

    2) ProInitPerformanceTest
          Initializing a performance Test which includes Freeing up the Procedure Cache & Data cache and hence Uses DBCC FREEPROCCACHE and 
          DBCC   DROPCLEANBUFFERS 

    and stll more...

    All these procedures are to be used by the QA team which uses the login "qalogin" (Not a member of sys_admin fixed group).  The proxy account solution applies only to the first one and that too problematic because Our windows domain user pasword expires in every 15 days and each time when the password changes I need to alter the Proxy Account.

    This is why I decided to create some procedures that will be executed in  the security context of 'sa' and not by the caller. But I found it is not possible.

    Then what is the use of  EXECUTE AS clause In SQL SEREVER ???

     



       

    Monday, December 21, 2009 7:20 AM
  • I think It cannot be solved in this way. Am I right?. Any reply will be a great help
    Tuesday, December 22, 2009 4:35 AM
  • If you are unable to have a windows account created without a freqently changing password, you will have difficulty using the xp_cmdshell proxy account.

    You are stymied in this pursuit as a result.
    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Tuesday, December 22, 2009 2:47 PM
  • The proxy account is usually a "service" account which does not follow normal password change policy, not a normal user account.  No user ever needs to know the password for this account.
    Tuesday, December 22, 2009 7:22 PM