I will grossly over-simplify it for you.

Create an app role - See BOL for syntax.

Everywhere the application opens a connection,
The connection object has to have the sp_setapprole procedure called to
"switch" the context to the application role with escalated rights in
the DB's.

So if you are lucky there is a class that has a method "GetConnection"
where this can be done in one place.
If the application is not using a common place to "get" a connection,
everywhere a connection is opened the sp_setapprole will need to be
executed immediately after the cn.Open() call.

Then, in the db, you allow DOM\Users to be valid logins only, with no
rights.
Assign all rights to the app-role. While a user could still connect
with QA, they could not do anything in the db....


Dano Smith




-----Original Message-----
From: Natalia Brett
[mailto:mssqldba-ezmlmshield-x52326139.[Email address protected]
Sent: Wednesday, October 26, 2005 11:55 AM
To: LazyDBA Discussion
Subject: SQL 2000 - Application role - Help!!!
Importance: Low


Hi,
our company has in-house application ("IDT") working with SQL 2000
database.
We use windows authentification.
I need to stop staff from accessing SQL server via any others
applications (such as Query Analyser, Enterprise Manager or Excel).

As I understand the Application role of SQL server should help, but I
dont undestand how to implement it.

Please,
Help!!!

The information within this e-mail is confidential and intended solely
for the use of the recipient(s). If you are not the intended recipient
then please notify Natalia Brett via email at Natalia.[Email address
protected] or by telephone on +44(0)1244 319912. Then delete the email
from your system. Please do not read, copy, print, forward, disclose, or
use the information contained within this email. Note that any views or
opinions presented in this email are solely those of the author and do
not necessarily represent those of Aktiv Kapital. Finally, the recipient
should check this email and any attachments for the presence of viruses.
The company accepts no liability for any damage caused by any virus
transmitted by this email.

Aktiv Kapital (UK) Ltd
Merchants House
Hamilton Place
Chester
CH12BE
UK
www.AktivKapital.co.uk



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page