Setting permissions for xp cmdshell for a user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Setting permissions for xp cmdshell for a user

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-17 : 22:30:54

I have this prcedure, how do I set the permissions to allow it to run from a standard user? If I add to the builtin\users the sysadmin server roles then it works however I just needed to allow access to the specific xp cmdshell command.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ServerList]

AS

SET NOCOUNT ON
Create Table #t([SQLServerList] nvarchar(100))
Insert into #t
Exec Master..Xp CmdShell 'Net Start'

Select dbo.Trim([SQLServerList]) AS SQLServerList
From #t
Where SQLServerList LIKE '%SQL SERVER (%'

Drop Table #t

SET NOCOUNT OFF

RETURN

I have a system running that invloves two PC's. The first is my sql server 2005 and the second I wish to run reports from report manager. So far I can run all of my reports from the second PC except those that use

EXEC Master xp cmdshell

. The error I get is as follows; Query execution failed for data set 'ServerList' (this is my SP). I should point out all reports run Ok from the sql server PC where I am logged in as the db owner and administrator. So far I have tried the following with no luck.

GRANT EXECUTE ON ServerList TO [BUILTIN\Users];GO
Use [MASTER] GRANT EXECUTE ON sys.xp cmdshell TO [BUILTIN\Users]; GO
 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

Under Security > Logins > BUILTIN\Users > User Mapping > Master DB set to BUILTIN\User with datareader, public and RSExecRole on.
If I add the builtin\users to the sysadmin server roles then it works however I just needed to allow access to the specific xp cmdshell and sys.master_files for example rather than everything.

Note I have taken the _ out of the xp cmdshell as if I put it in the post fails?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-18 : 13:26:22

If you want to return a list of all SQL Servers installed on the server, just use this call:


EXEC master..xp_regenumvalues 
    @rootkey='HKEY_LOCAL_MACHINE',
    @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

And if you want it in one table:


CREATE TABLE #ServerList (
    InstanceName SYSNAME NOT NULL,
    InstanceNumber SYSNAME NOT NULL
);

INSERT INTO #ServerList (InstanceName, InstanceNumber)
EXEC master..xp_regenumvalues 
    @rootkey='HKEY_LOCAL_MACHINE',
    @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

SELECT * FROM #ServerList;

DROP TABLE #ServerList;

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-18 : 17:57:31

Thankyou for your response however this question is more about security. I have several procedures that use xp cmdshell and other exec master..etc and when I run these reports from report manager they run on the server PC but fail from other PC's with normal users. I suspect your prcedure although a better approach gives me the same problem. So my question is what specific security objects do I need to allow and in what DB, mine or master? Under General Security BUILTIN\Users if I select sysadmin as a server role then all reports work from remote PC's with users but I really did not wont to open it up that much. I was surprised when I did this it did not fix it?
Use [Master]
GO
GRANT EXECUTE ON sys.xp cmdshell TO [BUILTIN\Users];
GO

quote:

Originally posted by tfountain

If you want to return a list of all SQL Servers installed on the server, just use this call:


EXEC master..xp_regenumvalues 
    @rootkey='HKEY_LOCAL_MACHINE',
    @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

And if you want it in one table:


CREATE TABLE #ServerList (
    InstanceName SYSNAME NOT NULL,
    InstanceNumber SYSNAME NOT NULL
);

INSERT INTO #ServerList (InstanceName, InstanceNumber)
EXEC master..xp_regenumvalues 
    @rootkey='HKEY_LOCAL_MACHINE',
    @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

SELECT * FROM #ServerList;

DROP TABLE #ServerList;

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-18 : 18:18:08

See above plus this is the error I get in the reporting services log.

aspnet_wp!webserver!14!11/19/2008-09:42:31:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'ServerList'. ---> System.Data.SqlClient.SqlException: 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.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-18 : 19:34:21

Ok, I figured you where just looking for a way to get the server names. With that in mind, there is no way to really limit what is executed via xp_cmdshell once you grant the permissions to execute it. The error message you received has the answer you need to resolve this - "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.".

To understand the permissions required for executing xp_cmdshell, read http://msdn.microsoft.com/en-us/library/ms175046(SQL.90).aspx.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources