by "Dan Guzman" <guzmanda@[EMAIL PROTECTED]
>
May 10, 2008 at 07:28 PM
> On a SQL Server 2000 db I would like to setup a stored procedure that
> accesses couple tables and runs the extended stored procedure
> xp..cmdshell. The goal is to grant users with limited privileges the
> right to run the stored procedure but not the rights to directly
> access either the referenced tables or the extended stored procedure.
If you tables are owned by 'dbo', you can accomplish this with
cross-database chaining as follows.
1) ensure your user database is owned by 'sa': EXEC sp_changedbowner
'sa'
2) enable the 'db chaining': EXEC sp_dboption 'MyDatabase', 'db
chaining',
true
3) Configure the SQL Agent proxy account by unchecking the 'only users
with
sysadmin ...' checkbox in Enterprise Manager under SQL Server
Agent-->Properties-->Job System. Make sure the proxy account has the
Windows permissions needed by the task xp_cmdshell performs.
Im****tantly, you should enable cross-database chaining in an sa-owned
database when only sysadmin role members can create dbo-owned objects.
See
the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"acw" <acwomble@[EMAIL PROTECTED]
> wrote in message
news:a41dacbd-df89-434b-83ed-1061e0380314@[EMAIL PROTECTED]
> On a SQL Server 2000 db I would like to setup a stored procedure that
> accesses couple tables and runs the extended stored procedure
> xp..cmdshell. The goal is to grant users with limited privileges the
> right to run the stored procedure but not the rights to directly
> access either the referenced tables or the extended stored procedure.
>
