Re: Ownership Chains Not Working?

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 06/22/04 

Date: Mon, 21 Jun 2004 21:47:23 -0600

Hi Ben,
Yes....all users in a database are members of the public
role and you can't delete users out of the public role. If
all users accessing the database do not need all the
privileges then it may be better to move those permissions
out of public and into a user defined role.

-Sue

On Mon, 21 Jun 2004 22:33:50 -0500, "Becker"
<ben@benbecker.net> wrote:

>Sue,
>
>Thanks for the response. My only problem is that the public profile in
>database A has all kinds of privileges that I don't want this user to have
>and I can't seem to revoke the public role for a user for a database? Seems
>public must be granted to all users for a database that will access it? I'm
>on SQL Server 7.
>
>Thanks,
>Ben
>
>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>news:558fd057if2vl481dth6uot53cica0cuin@4ax.com...
>> The user needs to be a valid user in both databases. The
>> user needs database access to the other database but you
>> don't need to grant object permissions as long as the
>> database chain is unbroken. This includes the database
>> owners, not just the objects involved.
>>
>> -Sue
>>
>> On Mon, 21 Jun 2004 16:26:49 -0500, "Becker"
>> <ben@benbecker.net> wrote:
>>
>> >I have two databases, say A & B.
>> >
>> >I have a table in database A called Table 1.
>> >
>> >I have a view in database B called View 1.
>> >
>> >B.View 1 selects * from A.Table 1.
>> >
>> >Both the view and the table are owned by dbo.
>> >
>> >I create a new user and grant him select on B.View 1 and database
>permission
>> >on only database B.
>> >
>> >When this user queries B.View 1, he gets an error saying he is not a user
>of
>> >database A. Why?
>> >
>> >I thought the chain of ownership for dbo for both table and view would
>allow
>> >this scenario to work? I don't want this user to be a member of the
>public
>> >role for database A, I only want him to be a member of database B.
>> >
>> >Any help would be appreciated!
>> >Thanks,
>> >Ben
>> >
>> >
>> >
>>
>

Relevant Pages

  • Re: PUBLIC ROLE
    ... think of the Public role as being very much ... > If I create a database called "TEST" and make a user George database ... you grant those permissions to the public role. ...
    (microsoft.public.sqlserver.security)
  • Re: Public Role in SQL Server 2000 SP3
    ... The public role is a special database role to which every database user ... Captures all default permissions for users in a database. ... Hari is a database owner and he create a table customer. ...
    (microsoft.public.sqlserver.server)
  • Re: new userID can do all...
    ... Being in the public role in a database shouldn't give you access to do ... >>>> No server roles have been assigned. ... >>> Does he have administrative rights to the box ...
    (microsoft.public.sqlserver.security)
  • Re: Remove permissions in default Public Role?
    ... functionality. ... in a database or when connecting to a database server. ... You'd want to make sure you are not granting permissions to ... happening because of the Public Role in the Master db. ...
    (microsoft.public.sqlserver.security)
  • ODBC+MFC; CRecordset::Update() syntax error or access violation
    ... select rights on the sysdatabases and the CRecorset::Updateis invoked. ... //We are connecting to the database using ODBC (DSNless connection) TCP/IP ... Everything was working fine until the select permission on the public role ...
    (microsoft.public.data.odbc)