Re: denying permissions on the view's underlying tables

From: Pete Ruby (pete.ruby_at_carris.net)
Date: 06/11/03 

Date: Wed, 11 Jun 2003 11:05:15 -0400

Brian,

I have an issue like yours. First, how do I find the SP of SQL 2000
(running NT4, SP6a). Second, where do I change that setting that Russell
mentions? i have databases on the same server.

For more info, see the thread "one SQL DB getting info form another - user
setup?"

Pete Ruby

"Bryan W. Brown" <bmail@imagin.net> wrote in message
news:052401c3205b$893c0270$a001280a@phx.gbl...
> That worked great!! We ran some tests and will be
> implementing it very soon.
>
> Thanks.
>
>
> >-----Original Message-----
> >Bryan,
> >
> >A couple of things.
> >
> >If the views are in the same database as the tables, I
> would have expected
> >it to work, as Andrew mentioned.
> >
> >Since you say that the views are in a separate database
> from the tables
> >then:
> >
> >1. With SP3 SQL Server 2000 will by default -break- the
> ownership chain when
> >passing between databases on the same server. This means
> that an ownership
> >chain that once worked will work no more if you have set
> this option in SP3.
> >
> >2. For linked servers, the link can be set up to run as a
> particular account
> >or with the rights of the person running the query. Look
> at
> >sp_addlinkedserverlogin. If the login between the
> servers is "useself" then
> >the access for 'Connie' is being checked for 'Connie'.
> If a particular
> >account is defined, then that account must have rights.
> >
> >Hope that gives you a start.
> >
> >Russell Fields
> >
> >"Bryan W. Brown" <bmail@imagin.net> wrote in message
> >news:0d8f01c31ec5$92829d90$a101280a@phx.gbl...
> >> Thanks,
> >> The views that I am using are pointing to different
> >> databases. Does this method of view and permission to
> the
> >> underlying tables only work if the views and the tables
> >> are in the same database.
> >>
> >> I am still getting the following error:
> >>
> >> Server: Msg 916, Level 14, State 1, Procedure
> >> BryanDistrictOffice, Line 9
> >> Server user 'Connie' is not a valid user in
> >> database 'TRANS'.
> >>
> >> The views are pointing to multiple multi-gigabyte
> >> databases.
> >>
> >> Thanks,
> >>
> >> Bryan
> >>
> >>
> >>
> >> >-----Original Message-----
> >> >As long as the view and the underlying tables are all
> >> owned by the same
> >> >owner you should only need to give select permissions
> on
> >> the view.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Greenoak" <bmail@imagin.net> wrote in message
> >> >news:08de01c31e0e$c9d068c0$a401280a@phx.gbl...
> >> >> When is Microsoft going to fix the problem of
> requiring
> >> >> users have permissions on the underlying tables of
> the
> >> >> views that they are using.
> >> >>
> >> >> We are using linked servers to create a data portal
> for
> >> >> the ad-hoc users. Unfortunately currently
> Microsoft's
> >> >> linked server technology is still lagging far behind
> >> their
> >> >> competitors so we have to place some very large
> >> databases
> >> >> on the same server as the "data portal database".
> >> >>
> >> >> The problem is that in addition to the views, we
> have to
> >> >> give permissions to the underlying tables. This is a
> >> real
> >> >> security problem and a frustrating one as we
> continue to
> >> >> implement data portals and linked servers.
> >> >>
> >> >> Does anyone know when this is going to get fixed. We
> >> >> currently have over 6,000 internal and several
> million
> >> >> external users.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • RE: Hiding MS SQL databases in Enterprise Manager
    ... Using Enterprise Manager does not inheritly mean that the user ... called to get a list of databases that the user has access to. ... This command takes no permissions into account, ... didn't use that nasty chunk of ad hoc SQL to get the list of databases. ...
    (Focus-Microsoft)
  • Re: SQL server does not exist or access denied
    ... and whatever access it needs in whichever databases. ... It sound like your windows service is going to access ... account - use User for the Account property. ... >I know but nevertheless the SQL profiler shows a user SYSTEM with loging ...
    (microsoft.public.sqlserver.connect)
  • SQL Authentication
    ... we've used the "sa" account for pretty much all access to the databases. ... Our SQL server and employee PCs are not part of the same active ... sign into 12+ SQL instances and manually update their password. ...
    (microsoft.public.sqlserver.security)
  • Re: wss backup fails.
    ... Does the SQL 2005 account have ... here is how the error shows in the Central Admin Backup and Restore ... databases residing on another server in the domain...or with the databases ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: BizTalk eating up SQL Server (Virii Behaviour)
    ... Yes, one gig is the least to run sql & bts in the same box, ... uninstall procedure, i manually deleted all databases, and run the ... and it was leaving like 20 mbs free memory, ... BizTalk Solution Developer ...
    (microsoft.public.biztalk.general)