"Mystery" <mystery_nospam@barrysworld.co.uk> wrote in message
news:uxp6LxqfDHA.576@tk2msftngp13.phx.gbl...
> We have our accounts package running on a Win2K server running SQL v8.
Can
> someone please tell me which security settings I need to configure in
order
> for a user to be able to connect to the tables in the SQL server via ODBC.
> I've tried configuring a User and System DSN from a workstation, and
> although I'm able to connect to the correct database, I'm unable to view
all
> the tables in the database. I'm using Windows authentication rather than
> the SQL one if thats any use to anyone?
>
> Help appreciate (by the way, it works no problem at all if the user is set
> up as an Administrator, hence it's got to be just a permissions problem)
>
> TIA.
>
>

"Andy S." <andy_mcdba@yahoo.com> wrote in message
news:%23Pc6y63fDHA.1872@TK2MSFTNGP09.phx.gbl...
> You should check the permissions on the table. By default, new tables do
> not have access to the public group which is what other users are logging
in
> as.
>
> Try picking a table you can see as sa, and doing
>
> GRANT SELECT on <tablename> TO PUBLIC
>
> in that database, then retry the ODBC as a user. There is SELECT, INSERT,
> UPDATE, DELETE levels of permissions for a table. Stored procedures have
> EXECUTE and permissions as well. If you use ALL for any database object,
> you get all available rights on it.
>
> To generate a grant permissions statement on all database objects quickly,
> run (I'm using ALL available permissions here, but you can do any level
you
> want from above)
>
> -- BEGIN SQL CODE
>
> SET NOCOUNT ON
> select 'GRANT ALL ON '+name+ ' to public' FROM sysobjects WHERE type in
> ('u','p','tr','s','v')
> SET NOCOUNT OFF
>
> -- END SQL CODE
>
> NOCOUNT prevents the count of results from being displayed. That way you
> can quickly highlight all the results with CTRL+SHIFT+END, then copy to a
> new window and run.
>
> 'u' = user table
> 'p' = procedure
> 'tr' = trigger
> 's' = system table
> 'v' = view
>
> There are more types, but you can see them in SQL books online.
> --
>
> **********************************************************
> Andy S.
> andy_mcdba@yahoo.com
>
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> **********************************************************
>
>
> "Mystery" <mystery_nospam@barrysworld.co.uk> wrote in message
> news:uxp6LxqfDHA.576@tk2msftngp13.phx.gbl...
> > We have our accounts package running on a Win2K server running SQL v8.
> Can
> > someone please tell me which security settings I need to configure in
> order
> > for a user to be able to connect to the tables in the SQL server via
ODBC.
> > I've tried configuring a User and System DSN from a workstation, and
> > although I'm able to connect to the correct database, I'm unable to view
> all
> > the tables in the database. I'm using Windows authentication rather
than
> > the SQL one if thats any use to anyone?
> >
> > Help appreciate (by the way, it works no problem at all if the user is
set
> > up as an Administrator, hence it's got to be just a permissions problem)
> >
> > TIA.
> >
> >
>
>

"Mystery" <mystery_nospam@barrysworld.co.uk> wrote in message
news:OGc$PaDgDHA.1752@TK2MSFTNGP10.phx.gbl...
> Thanks Andy.
>
> Whilst waiting for a response I realised that if I created a windows group
> and added it to the SQL Security Group Logins page and then allowed that
> group to have db_owner access to the relevant database, that also allowed
> them to view all the tables in the database. My question to you though is
> whether or not this is a 'good' way around the problem ie. is it leaving
me
> wide open to other possible security issues ?
>
> Matt.
>
> "Andy S." <andy_mcdba@yahoo.com> wrote in message
> news:%23Pc6y63fDHA.1872@TK2MSFTNGP09.phx.gbl...
> > You should check the permissions on the table. By default, new tables
do
> > not have access to the public group which is what other users are
logging
> in
> > as.
> >
> > Try picking a table you can see as sa, and doing
> >
> > GRANT SELECT on <tablename> TO PUBLIC
> >
> > in that database, then retry the ODBC as a user. There is SELECT,
INSERT,
> > UPDATE, DELETE levels of permissions for a table. Stored procedures
have
> > EXECUTE and permissions as well. If you use ALL for any database
object,
> > you get all available rights on it.
> >
> > To generate a grant permissions statement on all database objects
quickly,
> > run (I'm using ALL available permissions here, but you can do any level
> you
> > want from above)
> >
> > -- BEGIN SQL CODE
> >
> > SET NOCOUNT ON
> > select 'GRANT ALL ON '+name+ ' to public' FROM sysobjects WHERE type in
> > ('u','p','tr','s','v')
> > SET NOCOUNT OFF
> >
> > -- END SQL CODE
> >
> > NOCOUNT prevents the count of results from being displayed. That way
you
> > can quickly highlight all the results with CTRL+SHIFT+END, then copy to
a
> > new window and run.
> >
> > 'u' = user table
> > 'p' = procedure
> > 'tr' = trigger
> > 's' = system table
> > 'v' = view
> >
> > There are more types, but you can see them in SQL books online.
> > --
> >
> > **********************************************************
> > Andy S.
> > andy_mcdba@yahoo.com
> >
> > Always keep your antivirus and Microsoft software
> > up to date with the latest definitions and product updates.
> > Be suspicious of every email attachment, I will never send
> > or post anything other than the text of a http:// link nor
> > post the link directly to a file for downloading.
> > **********************************************************
> >
> >
> > "Mystery" <mystery_nospam@barrysworld.co.uk> wrote in message
> > news:uxp6LxqfDHA.576@tk2msftngp13.phx.gbl...
> > > We have our accounts package running on a Win2K server running SQL v8.
> > Can
> > > someone please tell me which security settings I need to configure in
> > order
> > > for a user to be able to connect to the tables in the SQL server via
> ODBC.
> > > I've tried configuring a User and System DSN from a workstation, and
> > > although I'm able to connect to the correct database, I'm unable to
view
> > all
> > > the tables in the database. I'm using Windows authentication rather
> than
> > > the SQL one if thats any use to anyone?
> > >
> > > Help appreciate (by the way, it works no problem at all if the user is
> set
> > > up as an Administrator, hence it's got to be just a permissions
problem)
> > >
> > > TIA.
> > >
> > >
> >
> >
>
>