Re: Assume SQL Server Rights for apps, any cons?

From: Mary Chipman [MSFT] (mchip_at_online.microsoft.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 13:18:56 -0500

Since your customer is the sysadmin on his SQL Server box, you need to
make sure that he is creating roles in his database and assigning
permissions only to roles, not to individual users. That way users can
be added/removed from roles based on the level of access he wants them
to have. Even if the role has only one user, he needs to set it up
this way. At runtime when a user first connects, your application can
retrieve the user's role membership, which you can cache locally in
your app and then use to enable/disable UI. If you try to do it any
other way, you'll end up with a big, hairy, un-maintainable mess on
your hands.

--Mary

On Mon, 21 Feb 2005 06:41:01 -0800, "Stephan Hoppe"
<StephanHoppe@discussions.microsoft.com> wrote:

>Hi,
>
>I’ve got a requirement from one of my clients and I am not sure if there are
>any cons. I am developing a data management software for him. He
>administrates the underlying SQL Server 2000 database by himself. Now he
>wants that my software assumes the user rights automatically from the SQL
>Server. Please let me give you a short example:
>
>Lets say we have a table called Customers. User A has the following rights
>- select (all columns)
>- update (only address fields)
>- insert (deny)
>- delete (deny)
>
>My application offers a GUI to manage customers. If user A opens the form
>the app should disable/hide the delete and insert buttons and disable all
>fields user A is not allowed to edit.
>
>My client wants to centralize the rights management what is not a bad idea.
>He wants to define roles and rights dynamically. He has some other
>applications who also read and edit data of this database so he doesn’t want
>to give users the right to edit all values of tables and fields.
>
>Do you see any cons?
>
>Look forward to hearing any cons :-)
>
>Stephan