Prev: multiple SQL Jobs running
Next: SQL 2008 - Policy Management
From: gracie on 6 May 2010 12:34

We have a 3rd party database that is weak when it comes to security.
In order to use a particular tool, users require access to custom db roles
which have upd/ins/del permissions on the back end database.
We have published this tool through citrix.

The business users now wish to access the database using MS Access to run
reports directly against some views in the database but they do not want to
use MS Access hosted on Citrix.

I can give them seperate accounts with only select access to the views but
how can I prevent them from connecting directly to the database with their
other account which have upd/ins/del permissions?
From: Mary Chipman [MSFT] on 7 May 2010 09:48
Since they are only running reports, create stored procedures that
return data, remove permissions from the base tables/views, and grant
Execute to the stored procedures. You can call the stored procedures
through pass-through queries, with return read-only result sets, and
base the reports on the pass-through queries. Even though you're using
Access, the ADO.NET topics on SQL application security might be
helpful -- http://msdn.microsoft.com/en-us/library/bb669057.aspx. Also
see http://www.sommarskog.se/grantperm.html#EXECUTE_AS.

At the end of the day the only way you can prevent them from
connecting directly to the database is to restrict access by revoking
permissions to the base tables or disabling their logins. However, if
you create a front-end using restricted permissions that gives them
access to all the read-only data they need, they probably won't see a
need to go off on their own.

--Mary

On Thu, 6 May 2010 09:34:01 -0700, gracie
<gracie(a)discussions.microsoft.com> wrote:

>
>We have a 3rd party database that is weak when it comes to security.
>In order to use a particular tool, users require access to custom db roles
>which have upd/ins/del permissions on the back end database.
>We have published this tool through citrix.
>
>The business users now wish to access the database using MS Access to run
>reports directly against some views in the database but they do not want to
>use MS Access hosted on Citrix.
>
>I can give them seperate accounts with only select access to the views but
>how can I prevent them from connecting directly to the database with their
>other account which have upd/ins/del permissions?
 | 
Pages: 1
Prev: multiple SQL Jobs running
Next: SQL 2008 - Policy Management