Re: SP accessing other db
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 08/15/05
- Next message: milney_boy: "Windows Authentication and COM DLLs"
- Previous message: Tom Moreau: "Re: Permission"
- 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: Mon, 15 Aug 2005 07:12:36 -0500
I'm glad you were able to figure out the problem. Please see my other reply
regarding the cross-database chaining server option. It's overkill to turn
on the option at the server level in your situation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
news:OAFhn2UoFHA.1044@tk2msftngp13.phx.gbl...
> I've found where was the problem. The other db was restored from another
> system.
> And its dbo user was blank even db Properties shows BUILTIN\Administrator
> as owner.
> sp_changedbowner() solved it.
>
> "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
> news:OipUyPUoFHA.3960@TK2MSFTNGP12.phx.gbl...
>>I did all, but it's still not working:
>> 1. Canceled to use dynamic sql's. Now the database name is hard-coded.
>> 2. Enabled cross-db chaining of the other db. I used Ent Manager:
>> DB Properties | Options | Allow cross-db chaining. Restart required? I
>> cannot
>> restart the server because it is 24h/7d working server.
>> 3. Created my own db with "EXEC sp_dboption 'mybase', 'db chaining',
>> 'ON'"
>> 4. The same login was used in creation of both db's. It is so important?
>> For example,
>> the other db was created by builtin Windows Admin account, I use SQL
>> account
>> with System Admin permission.
>> 5. Running the SP still raises an error: permission denied on an object
>> of the other db.
>>
>> SET @id = (SELECT CustID FROM OtherDb..CustTable WHERE...)
>>
>>
>>
>> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
>> news:OpNiGl7nFHA.2540@TK2MSFTNGP15.phx.gbl...
>>> 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?
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
- Next message: milney_boy: "Windows Authentication and COM DLLs"
- Previous message: Tom Moreau: "Re: Permission"
- 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
|
|
