Re: SP accessing other db
From: Tumurbaatar S. (spam_tumur_at_magicnet.mn)
Date: 08/15/05
- Previous message: Tumurbaatar S.: "Re: SP accessing other db"
- In reply to: Tumurbaatar S.: "Re: SP accessing other db"
- Next in thread: Dan Guzman: "Re: SP accessing other db"
- Reply: Dan Guzman: "Re: SP accessing other db"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 15 Aug 2005 12:58:10 +0900
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?
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
- Previous message: Tumurbaatar S.: "Re: SP accessing other db"
- In reply to: Tumurbaatar S.: "Re: SP accessing other db"
- Next in thread: Dan Guzman: "Re: SP accessing other db"
- Reply: Dan Guzman: "Re: SP accessing other db"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
