Background - Small firm / No DBA / Purchased package that stores data in
SQL Server 2005

We are developing a new reporting system with Access 2007. We want to
GUARANTEE that this new system will NEVER be able to update any SQL Server
tables.

No one has much SQL Server security experience, so we would like to know if
we are on the right track.



Here is what we are planning to do -

Set up new SQL Server Login (at the Server level)

Set up new SQL Server User (at the Database level) tied to the SQL Server
Login we just set up

Assign this new SQL Server User the role of db_datareader (and nothing else)

Change Server Authentication from “Windows Authentication Mode� to “SQL
Server and Windows Authentication Mode�
(Done at the Server Level)

Set up a new ODBC connection with our new Login and User

Tie the Access 2007 Reporting Application to this new ODBC connection

Are these the proper steps?



Also we have a question about one authentication overriding another…

Currently all users have Windows Authentication that permits them to update
the SQL tables in the purchased system.

If our new Access reporting system uses our new ODBC connection with the
User that only has db_datareader will this prevent any table updates via our
Access system, or will the original Windows Authentication that allows
updates override this and allow updates?

Thanks,
Brad

~~~~~~~~~~~~~~~~~~

On Thu, 20 May 2010 10:31:01 -0700, Brad
<(E-Mail Removed)> wrote:

You can of course test this out on a backup of your database, but yes
I believe your steps are correct.

If you're logging in as a sqlserver user, it does not matter that you
also have windows auth rights.

-Tom.
Microsoft Access MVP


>Background - Small firm / No DBA / Purchased package that stores data in
>SQL Server 2005
>
>We are developing a new reporting system with Access 2007. We want to
>GUARANTEE that this new system will NEVER be able to update any SQL Server
>tables.
>
>No one has much SQL Server security experience, so we would like to know if
>we are on the right track.
>
>
>
>Here is what we are planning to do -
>
>Set up new SQL Server Login (at the Server level)
>
>Set up new SQL Server User (at the Database level) tied to the SQL Server
>Login we just set up
>
>Assign this new SQL Server User the role of db_datareader (and nothing else)
>
>Change Server Authentication from “Windows Authentication Mode” to “SQL
>Server and Windows Authentication Mode”
>(Done at the Server Level)
>
>Set up a new ODBC connection with our new Login and User
>
>Tie the Access 2007 Reporting Application to this new ODBC connection
>
>Are these the proper steps?
>
>
>
>Also we have a question about one authentication overriding another…
>
>Currently all users have Windows Authentication that permits them to update
>the SQL tables in the purchased system.
>
>If our new Access reporting system uses our new ODBC connection with the
>User that only has db_datareader will this prevent any table updates via our
>Access system, or will the original Windows Authentication that allows
>updates override this and allow updates?
>
>Thanks,
>Brad
>
>~~~~~~~~~~~~~~~~~~

Tom,

Thanks for the help, I really appreciate it.

It looks like SQL Server security can become real complicated, especially
for people who are new to it. We sure don't want to make a mistake and leave
open a door in which someone with Access 2007 could change production data on
the database.

Thanks again,

Brad


"Tom van Stiphout" wrote:

> On Thu, 20 May 2010 10:31:01 -0700, Brad
> <(E-Mail Removed)> wrote:
>
> You can of course test this out on a backup of your database, but yes
> I believe your steps are correct.
>
> If you're logging in as a sqlserver user, it does not matter that you
> also have windows auth rights.
>
> -Tom.
> Microsoft Access MVP
>
>
> >Background - Small firm / No DBA / Purchased package that stores data in
> >SQL Server 2005
> >
> >We are developing a new reporting system with Access 2007. We want to
> >GUARANTEE that this new system will NEVER be able to update any SQL Server
> >tables.
> >
> >No one has much SQL Server security experience, so we would like to know if
> >we are on the right track.
> >
> >
> >
> >Here is what we are planning to do -
> >
> >Set up new SQL Server Login (at the Server level)
> >
> >Set up new SQL Server User (at the Database level) tied to the SQL Server
> >Login we just set up
> >
> >Assign this new SQL Server User the role of db_datareader (and nothing else)
> >
> >Change Server Authentication from “Windows Authentication Mode� to “SQL
> >Server and Windows Authentication Mode�
> >(Done at the Server Level)
> >
> >Set up a new ODBC connection with our new Login and User
> >
> >Tie the Access 2007 Reporting Application to this new ODBC connection
> >
> >Are these the proper steps?
> >
> >
> >
> >Also we have a question about one authentication overriding another…
> >
> >Currently all users have Windows Authentication that permits them to update
> >the SQL tables in the purchased system.
> >
> >If our new Access reporting system uses our new ODBC connection with the
> >User that only has db_datareader will this prevent any table updates via our
> >Access system, or will the original Windows Authentication that allows
> >updates override this and allow updates?
> >
> >Thanks,
> >Brad
> >
> >~~~~~~~~~~~~~~~~~~
> .
>

What prevents the user from creating a new database and creating a new ODBC
connection that doesn't use the restricted userid and password? Since they
are not logged in as an sqlserveruser won't the system connect them using
their integrated authority?

Brad <(E-Mail Removed)> wrote:

>Tom,
>
>Thanks for the help, I really appreciate it.
>
>It looks like SQL Server security can become real complicated, especially
>for people who are new to it. We sure don't want to make a mistake and leave
>open a door in which someone with Access 2007 could change production data on
>the database.
>
>Thanks again,
>
>Brad
>
>
>"Tom van Stiphout" wrote:
>
>> On Thu, 20 May 2010 10:31:01 -0700, Brad
>> <(E-Mail Removed)> wrote:
>>
>> You can of course test this out on a backup of your database, but yes
>> I believe your steps are correct.
>>
>> If you're logging in as a sqlserver user, it does not matter that you
>> also have windows auth rights.
>>
>> -Tom.
>> Microsoft Access MVP
>>
>>
>> >Background - Small firm / No DBA / Purchased package that stores data in
>> >SQL Server 2005
>> >
>> >We are developing a new reporting system with Access 2007. We want to
>> >GUARANTEE that this new system will NEVER be able to update any SQL Server
>> >tables.
>> >
>> >No one has much SQL Server security experience, so we would like to know if
>> >we are on the right track.
>> >
>> >
>> >
>> >Here is what we are planning to do -
>> >
>> >Set up new SQL Server Login (at the Server level)
>> >
>> >Set up new SQL Server User (at the Database level) tied to the SQL Server
>> >Login we just set up
>> >
>> >Assign this new SQL Server User the role of db_datareader (and nothing else)
>> >
>> >Change Server Authentication from “Windows Authentication Mode” to “SQL
>> >Server and Windows Authentication Mode”
>> >(Done at the Server Level)
>> >
>> >Set up a new ODBC connection with our new Login and User
>> >
>> >Tie the Access 2007 Reporting Application to this new ODBC connection
>> >
>> >Are these the proper steps?
>> >
>> >
>> >
>> >Also we have a question about one authentication overriding another…
>> >
>> >Currently all users have Windows Authentication that permits them to update
>> >the SQL tables in the purchased system.
>> >
>> >If our new Access reporting system uses our new ODBC connection with the
>> >User that only has db_datareader will this prevent any table updates via our
>> >Access system, or will the original Windows Authentication that allows
>> >updates override this and allow updates?
>> >
>> >Thanks,
>> >Brad
>> >
>> >~~~~~~~~~~~~~~~~~~
>> .
>>