Understanding SQL Server Security

Declan_Basile used Ask the Experts™
I need to set up SQL Server 2005 so that people can have read only access for adhoc querying, but when they log into an Access 2003 application they need to also have write permissions.  I'm planning to use SQL Server security and write the Access program to change the connection strings of the linked tables to use a username and password that has write access to the tables.  When a user does adhoc querying they would log in under a user account that has read-only permissions.  Is there a better way to accomplish this?  Also, I'm considering having just one account that everyone uses for read only permissions and one account for write permissions for when they are using an Access program.  Do you recommend instead that I have an account for each user?  If so, why?  Does SQL Server track who makes what changes to the database?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

You are doing ad hoc queries on a transaction database, why do you need an account that has write access? Allowing all users access to the base tables is not a good idea. Usually ad hoc queries are used for reporting. A better way would be to create views in SQL Server and let the users access that.

SQL Sercurity: if possible, use Windows authentication.

Yes, SQL Server does keep track of who makes changes. That information is kept in the log file. If you set up an audit trail you can maintain that information in a way that could be reported. If you are allowing any user write access to base tables you should have a way of finding out who changed a column.


The users need to enter/update/delete data by using the Office Access Applications that I wrote.  They also need to be able to link to SQL Server tables and create Access queries having read-only access when they do this.  How can I accomplish this without using SQL security?  I was going to give each user 2 SQL Server accounts.  One account would have read-only access and they could use it to write queries.  They would log into the Access programs under this same (read-only) account, but the Access program would change the connection strings of the linked tables to use the other account that has write permissions.  The user wouldn't even know about the other account that the Access programs use.  How else could I accomplish this?

OK, so you are not talking about ad hoc queries but day-to-day transactions?

I ask this because the concept of having users create queries to update/delete/insert data is odd. Usually the developer uses stored procedures in SQL Server for the every-day transactions.

Will your users know enough about how to write a query to do that? Will they want to? The users that I am familiar with just want to push a button. They would be lost trying to type "SELECT column FROM table...."

Back to your accounts idea:
You have a read-only account that the users logon to. This is the account that writes the queries.
Then, somehow, you want Access to change the connection string to a write-read account.

Why have them use the read-only account in the first place?

In the production transaction databases, with multiple users, that I have worked with, the developers create stored procedures that are called from the front-end application (VB, Access, ASP). The users have permissions to run those stored procedures and read-only access to views, so that they can create the necessary reports. Users never have read-write access to base tables.

In a multi-user database, having everyone write their own queries would probably cause locking and blocking issues since every transaction would not be the same procedure.


  The users will only create select queries using Microsoft Access's graphical query design tool.  They won't be writing queries to change any data.  When they need to change data, they will be using the Access programs that I wrote.  It's not that difficult for them to write queries.  Access makes it easy for them.  They don't need to know how to write SQL statements; the query design tool does that for them, and being able to write their own queries gives them great flexibility.
   I already have Access applications linked to Access tables, and I going to import the tables into SQL Server, link to them, and make minimal changes to the Access programs (at least initially).  I'm not planning to write stored procedures and rewrite the Access programs to execute them.  I don't think it would be worth it.  I plan to only change parts of the Access programs where performance is unacceptably slow, so I won't, at least initially, be writing stored procedures.  I want to give the users write access to the tables when using the access programs, just like they have now, only now the tables are in Access, not SQL Server.
<<In a multi-user database, having everyone write their own queries would probably cause locking and blocking issues since every transaction would not be the same procedure.>>
Users are writing their own select queries now with Access tables and have no problems, I don't expect for this to cause any problems if the tables are in SQL Server.
   Considering the situation, do you think there is a better way to accomplish what I'm trying to do?
Cool. I understand what you are doing now. Laughing, my users would revolt if our development group forced them to write their own queries (even if they are using the visual query tool in Access).


I do know that there are differences in the SQL between Access (which is ANSI-89) and SQL Server 2005 (ANSI-99 compliant).  

The issue that you may run into is speed, since these are linked tables, but that depends on your environment.

You will need to be sure that every table has a primary key (and that the primary key are the same or complimentary data type between Access and SQL Server). Access also likes a timestamp column.

With 2005, the Access experts are pushing the linked table technique over ADP. SQL Server 2005 does not fully support ADP. You may not be able to use all of the new features in 2005 and may consider changing the database compatibility mode to 2000.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial