Access Front-Sql Back--how set user rights?

Okay, so I have my database now in Access 2010, and my tables in SQL. But, how do I get user-level security? (for most this will be read-only, for about 2 this will be read-write).

I finally found this page that talks about creating a LOGIN:

http://www.quackit.com/sql_server/sql_server_2008/tutorial/sql_server_user_logins.cfm

not sure if this is what I need?

I have read that there are 2 authentication modes (Windows, vs Mixed); and that once you decide the authentication mode, users can be granted three levels of access into the database (login, user and object). But, I am still totally unsure of where do to this?

What I envision is that when users open up the front end (which is a copy of the original that sits on their desktop), the "system" will know what rights (to the SQL tables) they have. However, if that is not possible, then I'd just like to find out how I can get the prompt for UserID and password when Access opens.

thanks for any nods in the right direction,
Sarah

SQL server has two security modes, Windows authentication and mixed. If you users are accessing from the same domain, use Windows as users will not need to login manually.

To access data, a user needs to have a login and has to be a user of the database. Sql offers fine grain control over individual objects as well as providing database roles. So if you assign a user dbo role, they can do anything to the db. You don't want to do this though, as you need to operate the principle of least access. You may like to give them db_datareader access for instance. A good method of design is use stored procedures rather than directly inserting, updating etc and providing execute permissions on these.

The easiest way to manage permissions is via SQL Server Management Studio and use the GUI to assign the appropriate users, permissions etc.

Thank you , Pils

I did have a question about the stored procedures --do I use Mgmnt studio as a tool for adding these?

Also, you say "and use the GUI to assign the appropriate users". I assume the GUI you are referring to is the Mgmnt Studio, not Access.....?

thanks again,
SJL

A good method of design is use stored procedures rather than directly inserting, updating etc and providing execute permissions on these.

The easiest way to manage permissions is via SQL Server Management Studio and use the GUI to assign the appropriate users, permissions etc.

Yes, SSMS is the main tool for interacting with SQL server. I'm not sure how useful stored procs are for SQL with Access as a front end as I'm assuming you're binding directly to the SQL tables. I steer clear of Access whenever possible and usually use Visual Studio to build .Net front-ends using WPF or Silverlight - for these technologies it's usually a good idea to wrap all DML statements in stored procs for performance, security and ease of admin.

Note I said that the GUI is the easiest way of managing permissions, but it is not necessarily the best way. You can do this through SSMS. I would usually manage all DDL and DML
(have a look here http://blog.sqlauthority.com/2008/0...ml-ddl-dcl-and-tcl-introduction-and-examples/) by scripting. To develop in SQL you'll need to get a handle on T-SQL. My suggestion is getting in at the deep end and getting SSMS installed (there is a free express version) and start scripting. If you're familiar with SQL scripting in Access you'll have the basics already. If not, and you're a query designer user, I suggest binning that and getting your hands dirty with SQL.

You can also ease the admin of permissions by creating roles, assigning the appropriate permissions on these, and then assigning users to the role.

I am developing a client/server application with Access 2007 and SQL Server 2008 R2. This application also receives data from an AS/400 (DB2/400) which a Access based Replication process transfers those records and populates SQL Server with the records.

I am utilizing the SQL Server Management Studio to develop the SQL Server database, including the DB schema and also Stored Procedures (SP's). All interaction with SQL Server is conducted through Stored Procedures: SELECT / INSERT / UPDATE / TRUNCATE transactions.

I am executing them via two ways:

1) When I need to populate a FE DB temp table I am using nested DAO.QueryDef objects. (I use this for forms which list multiple records in Read-Only mode.) The inner object is configured in Pass-Through mode which sends the SQL directly to the BE DB. The outer wrapper selects each column from the inner query and maps it to the correct FE DB temp table column. (For my sanity I keep the col order the SAME.)

Example of this scenario:
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

2) For unbound record add/edit screens, I am using ADO objects First an ADODB.Command object with ADODB.Parameters objects is used to execute the SP which returns an ADODB.Recordset. When INSERT / UPDATE transactions are needed, a ADODB.Command / ADODB.Parameters are used to execute the SP and receive back the (a) unique ID of the newly stored record in the case of an INSERT or (b) the number of changed records (should be 1) if a record was being UPDATEd. If those SP's are successful then my code updates the FE temp table removing the old record (in the case of an UPDATE) and INSERT the record as-is from the BE DB. This is accomplished via 1) DAO objects explained above.

Examples of this scenario:
Using VBA ADO objects to execute a Stored Procedure (INSERT)
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

For UPDATE transactions, I supply back to the SP the logtimestamp column and the record unique ID which are both used to locate the record. If the SP can not find a matching record, then either someone else updated it, or if deletes are allowed then someone deleted it. This protects against multiple people editing the same record at the same time. "logtimestamp must mach, else update is not allowed". I am using the CURRENT_TIMESTAMP way of capturing the time the server thinks it is when the INSERT / UPDATE transactions are performed.

For the Access FE DB, I developed a CMD script which checks for existence of a directory on the local workstation, makes a clean copy of the FE DB, then launches it from the local HDD.

The application over all is very snappy / responsive.

Yesterday I have the opportunity to stress test my Client / Server architecture design as I worked remote via VPN connection for the afternoon.

More annoying than the slightly slower query response time was not having a second display! Access behaved very well connecting to the BE DB over a VPN.