How to run a Stored Procedure as Sysadmin?

Question

I don't want to grant the user Sysadmin role. Is there any way to run a specific stored procedure as Sysadmin or as a user that is sysadmin? The stored procedure is in MSSQL 2005 and has xp_cmdshell Below is the code:

ALTER PROCEDURE [dbo].[procExcelQuotebyItem] 
(
    @OrderNumber INT
)
AS

BEGIN
SET NOCOUNT ON


DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)

--  New File Name to be created
SET @fn = 'D:\Pre-Manufacturing\Excel\QuotebyItem.xls'

/*Cleanup*/

SET @Cmd = 'DEL ' + @fn
EXEC xp_cmdshell @Cmd, no_output

--  FileCopy command string formation
SET @Cmd = 'Copy D:\Pre-Manufacturing\Excel\QuotebyItemTemplate.xls ' + @fn

--  FileCopy command execution through Shell Command
EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT

--  Mentioning the excel destination filename
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @ExcelString = 'Excel 8.0;Database=' + @fn

EXEC('INSERT INTO OPENROWSET(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$A2:L2]'') 
SELECT [ITEMNUMBER],'''',[ITEM_DESCRIPTION],[CASEPACK],[UNIT PRICE],[CASE PRICE],[WEIGHT],[CUBE],[CASE DIMS],[UPC],[CASE UPC],[Q Comments] FROM [ORDER SUMMERY] WHERE [Order #] = ''' + @OrderNumber + '''')

Sparky · Accepted Answer · 2011-08-26T19:15:28.113

5

ALTER PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'USER_ID_with_sysadmin_rights'
AS

http://msdn.microsoft.com/en-us/library/ms188354(v=SQL.90).aspx

Test code

CREATE PROCEDURE dbo.test1
WITH EXECUTE AS 'CORP\jbooth'
AS
select user_name()
GO
EXEC test1
GO
ALTER PROCEDURE dbo.test1
WITH EXECUTE AS 'dbo'
AS
select user_name()
GO
EXEC test1

Additional info for gettting XP_CMDSHELL to work in SQL 2005

SQL 2005 XP_CMDSHELL

edited Aug 26 '11 at 19:15

answered Aug 26 '11 at 18:34

Sparky

14,967
2
31
45

I added a little test example, however I do not have SQL 2005 installed, just 2008. I did use this in version 2005, so it should work fine... – Sparky Aug 26 '11 at 18:51
As far as I remembered an object in a user database can't be given `EXEC AS ` a server role. Have you managed to get `xp_cmdshell ` to run? – Martin Smith Aug 26 '11 at 18:52
Could be some permission issue like that, it has been a while since I've used SQL 2005 and I can see the security implications. Worst case scenario, I guess he would need to create a user with the needed rights and assign the procedure to that user instead. I think his question is asking for that, a user that has SysAdmin rights.. (I'll modify the example to make it clearer) Thanks for the heads-up.... – Sparky Aug 26 '11 at 18:56
Msg 15151, Level 16, State 1, Procedure procExcelQuotebyItem, Line 49 Cannot execute as the user 'sysadmin', because it does not exist or you do not have permission. – Bruno Aug 26 '11 at 18:57
I changed the EXECUTE AS 'domain\user' and it gave the following error: `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'.` AND: `Msg 15274, Level 16, State 1, Line 1 Access to the remote server is denied because the current security context is not trusted.` – Bruno Aug 26 '11 at 18:59
What permissions does the user need to have? The particular user I tried had the sysadmin server role. – Bruno Aug 26 '11 at 19:00
Try GRANT EXECUTE rights to that procedure for the user. I remember that XP_ procedure have some security concerns, but I think GRANT EXECUTE should do it for you... – Sparky Aug 26 '11 at 19:02
EXECUTE AS 'domain\user' works and the xp_cmdshell runs, but now I get the following error when it tries to execute the INSERT INTO OPENROWSET: `Msg 15274, Level 16, State 1, Line 1 Access to the remote server is denied because the current security context is not trusted.` – Bruno Aug 26 '11 at 20:43
I would suggest creating a new question, I am very rusty on the security model of SQL 2005 and I would suggest you open that particular question up to the entire Stack Overflow community for a good answer... Sorry – Sparky Aug 26 '11 at 20:46
Thanks Sparky. Where would I place the GRANT EXECUTE at? `ALTER PROCEDURE [dbo].[procExcelQuotebyItem] ( @OrderNumber INT ) GRANT EXECUTE AS 'USer without permission' AS `? – Bruno Aug 29 '11 at 15:37
Found the answer: http://stackoverflow.com/questions/3374436/t-sql-create-user-and-grant-execute-on-permission-for-stored-procedures – Bruno Aug 29 '11 at 15:41

score 0 · Answer 2 · answered Aug 29 '11 at 15:59

0

I removed EXECUTE AS and added EXEC sp_addsrvrolemember 'Corporate\HelenS', 'sysadmin'; Works! Then I drop the role EXEC sp_dropsrvrolemember 'Corporate\HelenS', 'sysadmin'

answered Aug 29 '11 at 15:59

Bruno

6,211
16
69
104

1

I thought that in order to add a server role member, the user calling the stored procedure has to be a db_owner and sys_admin, in which case, your sp should already work for that user. – Mar 21 '12 at 17:48
1

This creates a huge potential security hole, and should not be an accepted answer. – Hamster Aug 26 '14 at 15:52

How to run a Stored Procedure as Sysadmin?

2 Answers2