Understanding SQL Server Security
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?
ASKER
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.