Re: Cross database granting
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 03/05/04
- Next message: khanhbui: "Re: convert to rows challenge"
- Previous message: Rohan: "RE: Error running stored procedure"
- In reply to: Chris Buys: "Re: Cross database granting"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 5 Mar 2004 08:48:53 -0600
To add to Tibor's remarks, you can perform the following steps once you've
configured the proxy account for non-sysadmin users. This will provide
restricted xp_cmdshell functionallity for your application.
1) enable cross-database chaining for your user database:
EXEC sp_dboption 'MyDatabase', 'db chaining', true
2) change the user database owner to 'sa':
USE MyDatabase
EXEC sp_changedbowner 'sa'
3) create a wrapper proc in your user database:
USE MyDatabase
GO
CREATE PROC usp_ExecuteSomeProgram
AS
EXEC master..xp_cmdshell 'MyProgram'
GO
GRANT EXEC on usp_ExecuteSomeProgram TO MyUser
GO
4) Use the above wrapper proc in your app.
Note that you should enable cross-database chaining only if you fully trust
users with permissions to create dbo-owned objects. This is especially true
for databases owned by 'sa'.
-- Hope this helps. Dan Guzman SQL Server MVP "Chris Buys" <chris@h-a-s.co.za> wrote in message news:u93lJwrAEHA.2300@TK2MSFTNGP10.phx.gbl... > Thanks for sticking with me so far. > > I've decided to add a new user (and custom login) > to the master database. > > But a new error occurs when I try to run the > procedure (logged in as that user): > > Msg 50001, Level 1, State 50001 > xpsql.cpp: Error 997 from GetProxyAccount on line 604 > > > When I login as sa, the problem doesn't occur. > I'm using MSDE2000 SP3. > > @@VERSION returns: > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) > Dec 17 2002 14:22:05 > Copyright (c) 1988-2003 Microsoft Corporation > Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 3) > > > Thanks > Chris > > -------------------------------------- > Chris Buys > Development > HAS Software (Pty) Ltd > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:#BWfHjrAEHA.916@tk2msftngp13.phx.gbl... > > Creating a user in master will not increase in any way that a login's > > connection will default to the master database. There already is a guest > > user in master, meaning that all logins can access master. > > > > You could enable cross database chaining ownership (whatever it is called, > > there's a new security setting introduced in sp3 you need to enable - read > > about it in sp3 readme) and then have the same database owner for the two > > databases and the user in the app database should be able to execute > > xp_cmdshell implicitly if called from a proc in the app database. > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > > > > > "Chris Buys" <chris@h-a-s.co.za> wrote in message > > news:uJ11k1qAEHA.2800@tk2msftngp13.phx.gbl... > > > Can I achieve the same effect without creating a user > > > for the master database? I don't want the user to inadvertently > > > default to the master database. > > > > > > -------------------------------------- > > > Chris Buys > > > Development > > > > > > HAS Software (Pty) Ltd > > > > > > > > > > >
- Next message: khanhbui: "Re: convert to rows challenge"
- Previous message: Rohan: "RE: Error running stored procedure"
- In reply to: Chris Buys: "Re: Cross database granting"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
