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'

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Any idea guys ??

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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'.

 

Answer 9

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

Answer 10

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 ???

 

   

Answer 11

I think It cannot be solved in this way. Am I right?. Any reply will be a great help

Answer 12

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

Answer 13

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.