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.

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.
>
>

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.
> >
> >
>
>

I've now removed the db_owner setting for the group and set the SELECT flag
on each table individually that the ODBC conenction needs to be able to
view. This works great and wouldn't appear to be compromising my security
for the database.

Thanks.

"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.
> > >
> > >
> >
> >
>
>