Re: Read-only Reports User

From: Andrew J. Kelly (akelly@targitinteractive.com)
Date: 11/07/02 

From: "Andrew J. Kelly" <akelly@targitinteractive.com>
Date: Thu, 7 Nov 2002 16:46:36 -0500

Cindee,

I was just graciously informed that I had my head somewhere it shouldn't
have been when I answered one part of your question. I believe the users
still need access to the underlying tables when a view is cross db. You
best bet may be to script the permissions as I mentioned in the other parts.
Sorry about that. 

-- 
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.
Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm
"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message
news:ebD238ohCHA.1960@tkmsftngp11...
> > a.  They only see these 20 or so tables in the LINK table
> > window
>
> Not possible directly.  They will see all the objects but will only be
able
> to actually read from the ones you permit.
> You can create a separate DB with views that point to the tables in the
> other db's and give them select permission on the views and point them to
> that db only.  Then they only see those objects and not all the rest.
Not
> particularly pretty but one of the only way's I know of to stop them from
> seeing the other tables.
>
>
> > b.  Their access is READ-ONLY to those tables
>
> Create a ROLE and assign these users to that role.
>
>
> > My problem is that this will turn into an all day job to
> > open each database and go to the Reports user's Permission
> > and then put an X in each box (except Select) on all 500
> > tables.
>
> Use tsql such as GRANT to do this.  You can copy and paste the code very
> easily.
>
> -- 
> Andrew J. Kelly   SQL MVP
> Targitinteractive, Inc.
>
> Check out the PASS Community Summit - Seattle, the largest and only user
> event entirely dedicated to SQL Server, November 19-22.
> http://www.sqlpass.org/events/seattle/index.cfm
>
>
> "Cindee Roby" <croby@ohlogistics.com> wrote in message
> news:83a301c2867b$dd07a3c0$37ef2ecf@TKMSFTNGXA13...
> > We use MS Access and Crystal for reporting on our MS Great
> > Plains SQL data.  We have about 15 different databases
> > with approx 500 tables in each database.  Of all these
> > tables, users need Read Only access to about 20 of them in
> > each DB for reports.  I created a Reports user (we only
> > need one at a time) that they use to log into the data
> > through an ODBC connection.  I want to ensure a couple of
> > things:
> >
> > a.  They only see these 20 or so tables in the LINK table
> > window
> > b.  Their access is READ-ONLY to those tables
> >
> > My problem is that this will turn into an all day job to
> > open each database and go to the Reports user's Permission
> > and then put an X in each box (except Select) on all 500
> > tables.
> >
> > There's got to be a quicker way....  anyone know it?
> >
> > Thanks a lot!
> >
> > ~Cindee
>
>

Relevant Pages

  • Re: MS Access Reports and VB.NET Program
    ... Lets say he did upgrade his Access database to Sql Server. ... Unlock your mind sir. ... rewriting reports in the future. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: MS Access Reports and VB.NET Program
    ... Lets say he did upgrade his Access database to Sql Server. ... mind he's using .NET too write his client app. ... rewriting reports in the future. ...
    (microsoft.public.dotnet.languages.vb)
  • Optimal configuration for report generator
    ... suffers terribly when several users try to run reports in parallel. ... I am also looking at the effect of database size on performance, ... I added 3 G more memory to the system, and modified boot.ini to include the ... - in the SQL Server memory configuration, ...
    (microsoft.public.sqlserver.server)
  • Optimal configuration for report generator
    ... suffers terribly when several users try to run reports in parallel. ... I am also looking at the effect of database size on performance, ... I added 3 G more memory to the system, and modified boot.ini to include the ... - in the SQL Server memory configuration, ...
    (microsoft.public.sqlserver.programming)
  • Optimal configuration for report generator
    ... suffers terribly when several users try to run reports in parallel. ... I am also looking at the effect of database size on performance, ... I added 3 G more memory to the system, and modified boot.ini to include the ... - in the SQL Server memory configuration, ...
    (microsoft.public.sqlserver.datawarehouse)