Re: Ownership Chains Not Working?

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 08:23:04 -0500

I agree with Sue. Personally, I grant permissions only to user-defined
roles and never grant permissions to public.

If you must keep your public role permissions, you can create a role in
DatabaseA for your DatabaseB users and DENY the unwanted permissions to that
role. Remember that DENY always takes precedence over GRANT. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Becker" <ben@benbecker.net> wrote in message
news:eVGlAnAWEHA.1380@TK2MSFTNGP12.phx.gbl...
> 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: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)
  • Re: User access on a company intranet
    ... Yes they need full permissions on the folder where the backend is. ... You wouldn't need to do this in your copy of the database. ... However you can toggle the shiftkey bypass from another mdb file. ... When you want to implement security, you create a new mdw file, ...
    (microsoft.public.access.security)
  • Re: Active directory corruption
    ... During an installation of PHP I accidentally changed permissions for the ... Active Directory database is unavailable because it is damaged, ... Open a command prompt and run NTDSUTIL to verify the paths for the ...
    (microsoft.public.windows.server.sbs)
  • Re: Active directory corruption
    ... default web site and copied the permissions to all the child ... as it may not be the database that is the problem. ... prompt, use the ESENTUTL to check the integrity of the database. ... To recover the database type the following at the command prompt: ...
    (microsoft.public.windows.server.sbs)
  • Problem is w/ .ADP..Re: SQL db Permissions for users not working
    ... You do not have SELECT permissions on the ... SysObjects system table in the database. ... figured out that qualifying the database owner (dbo in my ... >> I feel that the object owner is not dbo, ...
    (microsoft.public.sqlserver.security)