Read only access to microsoft sql server 1

[mechamd]

Is there a read only odbc driver for microsoft sql server? Or how can I ensure that an odbc connection is read only?

 

[TomSark]

I haven't heard of a read-only property for an ODBC connection. Howver, there are a number of things on the SQL Server side that you can do to ensure read-only activity.

You can make the database read-only using:
sp_dboption 'your database name','read only', 'true

You can implement security on SQL Server such as:
* Assign account(s) to belong to the DB_DATAREADER role, giving them read-only access, and connect with those account(s).
* Assign accounts/roles to have SELECT permissions to tables.

etc. etc. etc.

If you could provide more details as to why and how you are wanting to do this, it would be easier to give a more direct answer...

Tom

 

[mechamd]

We have individuals that are accessing data on an sql server directly through access 97 rather than using the front end program. This allows these people to see and modify data directly without regard to security. I've seen odbc drivers that have a read only option and now I can't find them. Either way, these people need access to the database, but we need to protect the data.

 

[TomSark]

Well, I would suggest that you add their accounts to the DB_DATAREADER fixed database role for this database and remove any other permissions that they have. In this way, they won't be able to do anything but select from the tables...

Tom

 

[foxdev]

Tom's right, if they should always be in readonly mode; but if they need write access through the "front end program", then you may be able to employ Application Security Roles. You would revoke all of their permissions, then have the front end program login using the application role.

Robert Bradley

http://www.foxdev.com/