Re: Application role to access xp_cmdshell

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 02/17/04 

Date: Tue, 17 Feb 2004 08:06:28 -0600

As long as the ownership chain is unbroken, direct permissions on
xp_cmdshell are not needed. This necessitates that your user procs be owned
by 'dbo', your user database be owned by 'sa' and cross-database chaining
(intoduced in SQL 2000 SP3) be enabled. Example script below.

For security reasons, it is important that your user proc be coded in such a
way that only the intended command can be executed. Also, you should enable
cross-database chaining only if you fully trust users that have permissions
to create dbo-owned objects. See Cross-database chaining in the SQL 2000
Books Online for more information.

You will also need to allow non-sysadmin users to execute xp_cmdshell. You
can do this from Enterprise Manager under Management-->SQL Server
Agent-->Job System. Uncheck the 'Only users with sysadmin privileges...'
check box and specify the Windows account you want to use as the OS security
context for non-sysadmin users. This account should have the minimal
permissions need to perform the needed tasks.

> I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.

You'll need to execute sp_setapprole directly from your application. From
the Books Online:

<Excerpt href="tsqlref.chm::/ts_sp_sa-sz_6tt1.htm">
The sp_setapprole stored procedure can be executed only by direct
Transact-SQL statements; it cannot be executed within another stored
procedure or from within a user-defined transaction.
</Excerpt>

USE MyDatabase
EXEC sp_changedbowner 'sa'
GO

-- for SQL 2000 SP3+
EXEC sp_dboption 'MyDatabase', 'db chaining', true
GO

CREATE PROC dbo.MyXpCmdShellProc
AS
EXEC master..xp_cmdshell 'MyCommand'
GO
GRANT EXEC ON dbo.MyXpCmdShellProc TO MyAppRole
GO

EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'
EXEC dbo.MyXpCmdShellProc
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@regerar.com> wrote in message
news:d495b147.0402170439.d8b1453@posting.google.com...
> I have an Access app linked to a SQL server db. This app uses
> xp_cmdshell in some stored procedures and it works fine. As long as
> the user is administrator... I'd like to set up an application role
> that can execute xp_cmdshell and access my db but I don't know how to
> do it as xp_cmdshell is in the Master db while everything else is in
> my own db. I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
>
> Can somebody please give me some code examples or direct me to a good
> site?
>
> /Carl

Relevant Pages

  • xp_sqlagent_proxy_account: Specified user cannot login
    ... I am trying to configure SQL so that a user without admin privileges can ... execute xp_cmdshell. ... EXEC master..xp_sqlagent_proxy_account N'SET' ...
    (microsoft.public.sqlserver.security)
  • Re: Need to make Sort ORDER in SP defined by Input variable
    ... If I enter 'ISBN' when I execute the SP below, it executes, ... SET NOCOUNT ON ... DECLARE @SQL nvarchar ... > exec sp_executesql @SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: use of INSERT with Dynamic SQL
    ... > I supose the only way is to use dynamic SQL. ... > Is there a way to execute an INSERT statemente with dynamic SQL? ... > EXEC spInsertMyTable ...
    (microsoft.public.sqlserver.programming)
  • set @sql = Ncreate table #tmp_table(guarid int)
    ... When I execute: ... exec sp_executesql @sql ... if I change the temp table to a global table it runs ...
    (microsoft.public.sqlserver.programming)
  • RE: Function writting challange!!
    ... You can not execute sp_executesql from inside a function (may be there is ... back door), but the question here is why a function and not a stored ... > SQL 2000 ... > exec sp_executesql @qq ...
    (microsoft.public.sqlserver.programming)