Re: SQL Rights Issue
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 04/30/04
- Next message: Dan Guzman: "Re: Newbee password question"
- Previous message: sean: "Data Encryption"
- In reply to: C.P.: "SQL Rights Issue"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 29 Apr 2004 20:17:37 -0500
To allow a non-sysadmin role member to execute xp_cmdshell, you need to
uncheck the 'Only users with Sysadmin privileges...' checkbox in Enterprise
Manager. You can find it by right-clicking on SQL Agent, selecting
Properties and then clicking the Job System tab.
When you uncheck the box in SQL 2000, you'll be prompted for a SQL Agent
Proxy account. This is the Windows account that will be used as the OS
security context when non-sysadmin users execute xp_cmdshell. Ensure the
proxy account has the permissions needed to run your EXE.
You might consider encapsulating xp_cmdshell command need by your app in a
user proc and executing that procedure instead. This way, you don't need to
grant users direct execute permissions on xp_cmdshell. You can do this as
follows:
1) create the user proc (owned by dbo) in your user database
2) grant execute permission on the user proc
3) change your user database owner to 'sa' (EXEC sp_changedbowner 'sa')
4) enable cross-database chaining in your user database (EXEC sp_dboption
'MyDatabase', 'db chaining', true)
Note that you should enable cross-database chaining only if you fully trust
those users who have permissions to create dbo-owned objects.
-- Hope this helps. Dan Guzman SQL Server MVP "C.P." <craig_parrish@dontspamme.hotmail.com> wrote in message news:FB030ACD-B807-49DC-81DB-307787FB2A8F@microsoft.com... > We have a custom program that is calling the extended stored procedure xp_cmdshell. The only way we've been able to get it to successfully run is by giving the user the System Administrator server role. > > We tried setting the user up to have the DBO role on the master database and giving the user exec rights to xp_cmdshell, but that still did not allow it to run. > > We don't want to assign the System Administrator role to every user who has to run this program. What alternatives do we have? > > Thanks in advance for your help. > > If you would, please email me with posting to this, I might not be able to find my way back again :) > > craig_parrish@snipthispart.hotmail.com >
- Next message: Dan Guzman: "Re: Newbee password question"
- Previous message: sean: "Data Encryption"
- In reply to: C.P.: "SQL Rights Issue"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
