Controlling User Privileges

Hi,
I'm designing an application which uses SQL Server 2000 as
the database. At the application level, I have features to
maintain & control application level users.
Approach-1
Users login to the application using their application
logins and the application will authenticate their
privileges. But internally the application use a SINGLE
SQL Server login to access the database(s) in the server.
Approach-2
At the SQL Server level, we create logins and database
users for each application user and map them. So depending
upon the application user, the corresponding sql server
login/user will be used to access the database(s).
Now, I want to assess the Pros & Cons of these two
approaches.
Sorry for making this posting so big.
Expecting guidance and pointers to any relevant resources.
TIA,
HariHave you considered 3rd possibility - using trusted authentication. i.e.
Windows users and groups? Check the "Managing Security" topics in Bokks
OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:09c701c3a421$b9541fc0$a101280a@.phx.gbl...
> Hi,
> I'm designing an application which uses SQL Server 2000 as
> the database. At the application level, I have features to
> maintain & control application level users.
> Approach-1
> Users login to the application using their application
> logins and the application will authenticate their
> privileges. But internally the application use a SINGLE
> SQL Server login to access the database(s) in the server.
> Approach-2
> At the SQL Server level, we create logins and database
> users for each application user and map them. So depending
> upon the application user, the corresponding sql server
> login/user will be used to access the database(s).
> Now, I want to assess the Pros & Cons of these two
> approaches.
> Sorry for making this posting so big.
> Expecting guidance and pointers to any relevant resources.
> TIA,
> Hari|||Hi Hari,
Hope you have considered the number of users logging in and the kind of changes they are going to make through your application?
If the users are going to be Windows Domain users, then you can go for what Dejan has suggested. And if the users are in large numbers and do not have windows domain authentication, it is better to go with your first approach. Beware that you will not know what changes the users are going to make, unless you are logging it somewhere at the application level.
And the second approach will be useful if you want to control user access at the database level.
Thanks
GYK
-- Dejan Sarka wrote: --
Have you considered 3rd possibility - using trusted authentication. i.e.
Windows users and groups? Check the "Managing Security" topics in Bokks
OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:09c701c3a421$b9541fc0$a101280a@.phx.gbl...
> Hi,
>> I'm designing an application which uses SQL Server 2000 as
> the database. At the application level, I have features to
> maintain & control application level users.
>> Approach-1
>> Users login to the application using their application
> logins and the application will authenticate their
> privileges. But internally the application use a SINGLE
> SQL Server login to access the database(s) in the server.
>> Approach-2
>> At the SQL Server level, we create logins and database
> users for each application user and map them. So depending
> upon the application user, the corresponding sql server
> login/user will be used to access the database(s).
>> Now, I want to assess the Pros & Cons of these two
> approaches.
>> Sorry for making this posting so big.
>> Expecting guidance and pointers to any relevant resources.
>> TIA,
> Hari