locked
SQL Server access -Active Directory vs Single in database table RRS feed

  • Question

  • What are the pros and cons of using Active Directory to manage access for the database  and using a table inside the database to manage access?

    If using the table can someone with Excel open the database easier?

    To manage delete, insert, update, select  per table per column  does that need to be managed one way or the other?

    Thursday, June 3, 2010 10:46 AM

Answers

  • Here is a Best Practices document on SQL Server Security.  If you look for the Authentication heading, it recommends using Windows Authentication.

    http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

    If you have many people with the same rights, you could create Windows Groups, and make those the logins and users in your database.  All domain members of the Windows Group can get access via the group rights.  This gives you fewer places to do the grants.

    However, you also mention managing usage 'per column', so you seem to be looking at column level security.  If your needs are simple, you could create views that contain only the necessary columns, and grant rights to use those.  (Instead of using the underlying table.)  However, if your security model is more complex, read this paper about using some table driven security:

    Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005
    http://msdn.microsoft.com/en-us/library/cc966395.aspx

    Of course, security is a complex issue, so you need to understand your overall goals.  If you need some of the tips in the paper it does not mean that you need them all.

    FWIW, I have a database with row and column security, based on views and some internal security tables.  My security tables are based upon Windows Logins and Windows Groups to determine the rights of the individual user. 

    RLF

    Thursday, June 3, 2010 2:25 PM