Re: SP accessing other db
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 08/13/05
- Previous message: Tumurbaatar S.: "Re: SP accessing other db"
- In reply to: Tumurbaatar S.: "Re: SP accessing other db"
- Next in thread: Tumurbaatar S.: "Re: SP accessing other db"
- Reply: Tumurbaatar S.: "Re: SP accessing other db"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 12 Aug 2005 22:44:13 -0500
Out of curiosity, why do you pass the database name as a parameter? It
seems to me you can simply use a hard-coded 3-part name
(dbname.dbo.SomeTable).
Note that you'll also need to enable cross-database chaining ('db chaining'
database option) in these databases so that permissions aren't needed on
objects in the other database. Also, the databases need to have the same
owner so that the dbo users map to the same login. See the Books Online for
security considerations with cross-database chaining.
-- Hope this helps. Dan Guzman SQL Server MVP "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message news:uapIAH7nFHA.1204@tk2msftngp13.phx.gbl... > The problem that the 1st db is under my control: I designed and created > it. > But the 2nd one is a db from some 3rd party application. We're adding > an additional functionality to it, but trying to avoid modifications on > the original db. > Or at least a modification should be as small as possible. > And now I see only one way: add one more SP but on the 2nd db. > The 1st SP calls the 2nd SP, the user has only EXEC permission, no other > rights. > > > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:OB5jhA0nFHA.3256@tk2msftngp13.phx.gbl... >> Permissions on objects referenced by the dynamic SQL statement are >> required. SQL Server can make no assumptions about the executed statement >> so dynamic SQL always breaks the ownership chain. >> >> The larger issue is why you need to use multiple databases. I would >> think it would be possible to use a single database with an additional >> column to logically partition the data. This would allow you to leverage >> ownership chain security so that permissions on indirectly referenced >> objects are not needed. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message >> news:u5cCvgynFHA.3256@TK2MSFTNGP12.phx.gbl... >>> The user has no any roles on the 2nd db. >>> SP generates a dynamic sql and executes it via EXEC. >>> And permission denied. >>> >>> I use a dynamic sql because a name of the 2nd db >>> passed thru parameter (e.g. @dbname + '.dbo.SomeTable'). >>> And now I wonder how to solve it. The 2nd db contains >>> important data and I don't want to give any permissions >>> to this user. >>> >>> >>> "Uri Dimant" <urid@iscar.co.il> wrote in message >>> news:OWUwYlxnFHA.320@TK2MSFTNGP09.phx.gbl... >>>> Hi >>>> Does the user have sysadmin perm or a member db_owner database role? >>>> >>>> Does the SP use dynamic sql inside? >>>> >>>> Are you unable to check it by yourself? >>>> >>>> >>>> >>>> >>>> >>>> >>>> "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message >>>> news:uysyVexnFHA.3036@TK2MSFTNGP14.phx.gbl... >>>>>A login has access to 2 databases in SQL2K server. The 1st db has >>>>> a stored procedure (SP) which modifies (INSERT) some table in the 2nd >>>>> db. >>>>> The user has EXEC permission on this SP, but no any permission >>>>> on objects of the 2nd db (default public role). >>>>> Is it enough? Or I should add INSERT permission on the 2nd db's table? >>>>> >>>> >>>> >>> >>> >> >> > >
- Previous message: Tumurbaatar S.: "Re: SP accessing other db"
- In reply to: Tumurbaatar S.: "Re: SP accessing other db"
- Next in thread: Tumurbaatar S.: "Re: SP accessing other db"
- Reply: Tumurbaatar S.: "Re: SP accessing other db"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
