none
Security for Access Front End SQL Server Back End RRS feed

  • Question

  • Hi,

    I'm hoping someone can give me a point in the right direction here.

    I have created an Access 2010 front end to a SQL Server 2008 back end.  I'm getting ready to install the SQL Server back end on the server, and the Access front end on individual machines.  Generally speaking, how do I go about setting up users?  I understand (generally) that I can create users in SQL Server and assign permissions, but how do I get the users to sign into the front end?

    I hope this makes some sense - this is my first time using SQL Server, and my first time setting up users in general, so any help is much appreciated.

    Thanks.

    Wednesday, March 14, 2012 8:30 PM

Answers

  • Hi,

    I'm hoping someone can give me a point in the right direction here.

    I have created an Access 2010 front end to a SQL Server 2008 back end.  I'm getting ready to install the SQL Server back end on the server, and the Access front end on individual machines.  Generally speaking, how do I go about setting up users?  I understand (generally) that I can create users in SQL Server and assign permissions, but how do I get the users to sign into the front end?

    I hope this makes some sense - this is my first time using SQL Server, and my first time setting up users in general, so any help is much appreciated.

    Thanks.

    Google 'Windows Integrated Security' and 'SQL Server Security Active Directory Groups'. The concept is to manage permissions via Active Directory groups. This will require you to list out everyone who needs access and determine the groups that you'll need for permissions such AD-GROUP-ADMINS, AD-GROUP-READONLY, AD-GROUP-ORDER_ENTRY, AD-GROUP-REPORTING, etc.

    Once you've identified the AD groups that you'll need, you'll create them in AD with the associated users. Then you'll add each AD Group as a User under Security for the applicable database in SQL Server Management Studio. From there you'll grant/revoke rights to the specific AD GROUPS in SQL SMS. Once setup, you'll pretty much never have to deal with security within SQLSMS again. You'll manage it by adding or removing a user from the associated AD group in Active Directory. This *seriously* cuts down on maintenance as the person's user id doesn't have to be deleted out of SQLSMS when they leave or when their role changes. www.sqlservercentral.com is a great resource when it comes to all things SQL Server and a better source than here.


    David H

    • Marked as answer by RKMConsulting Thursday, March 15, 2012 6:04 PM
    Wednesday, March 14, 2012 10:47 PM
  • Regarding AD groups, check this out:

    http://www.accesssecurityblog.com/post/Securing-Access-databases-using-Active-Directory.aspx


    -Tom. Microsoft Access MVP

    • Marked as answer by RKMConsulting Thursday, March 15, 2012 6:05 PM
    Thursday, March 15, 2012 4:31 AM

All replies

  • Hi,

    I'm hoping someone can give me a point in the right direction here.

    I have created an Access 2010 front end to a SQL Server 2008 back end.  I'm getting ready to install the SQL Server back end on the server, and the Access front end on individual machines.  Generally speaking, how do I go about setting up users?  I understand (generally) that I can create users in SQL Server and assign permissions, but how do I get the users to sign into the front end?

    I hope this makes some sense - this is my first time using SQL Server, and my first time setting up users in general, so any help is much appreciated.

    Thanks.

    Google 'Windows Integrated Security' and 'SQL Server Security Active Directory Groups'. The concept is to manage permissions via Active Directory groups. This will require you to list out everyone who needs access and determine the groups that you'll need for permissions such AD-GROUP-ADMINS, AD-GROUP-READONLY, AD-GROUP-ORDER_ENTRY, AD-GROUP-REPORTING, etc.

    Once you've identified the AD groups that you'll need, you'll create them in AD with the associated users. Then you'll add each AD Group as a User under Security for the applicable database in SQL Server Management Studio. From there you'll grant/revoke rights to the specific AD GROUPS in SQL SMS. Once setup, you'll pretty much never have to deal with security within SQLSMS again. You'll manage it by adding or removing a user from the associated AD group in Active Directory. This *seriously* cuts down on maintenance as the person's user id doesn't have to be deleted out of SQLSMS when they leave or when their role changes. www.sqlservercentral.com is a great resource when it comes to all things SQL Server and a better source than here.


    David H

    • Marked as answer by RKMConsulting Thursday, March 15, 2012 6:04 PM
    Wednesday, March 14, 2012 10:47 PM
  • Regarding AD groups, check this out:

    http://www.accesssecurityblog.com/post/Securing-Access-databases-using-Active-Directory.aspx


    -Tom. Microsoft Access MVP

    • Marked as answer by RKMConsulting Thursday, March 15, 2012 6:05 PM
    Thursday, March 15, 2012 4:31 AM
  • Thank you both so much, this is enormously helpful.  I may have questions once I'm able to fully absorb these concepts, but this is exactly the kind of advice I needed.
    Thursday, March 15, 2012 2:01 PM