Read-Only access after using Upsizing Wizard

[sxam]

Hello.
I have the following problem. In our project we decided to use Access as link to SQL Server. So, we used Upsize Wizard to migrate our tables to SqlServer's tables. It was successful.
But for some users i wanted to grant read-only permissions for all data. Before the migrating i used to open Access app. with a script and write "/ro" - that gave only read-only permissions. But now user still can go inside the linked tables and write there! How can i prevent this from a script? Thanks in advance,

[Jim Doherty]

Hello.
I have the following problem. In our project we decided to use Access as link to SQL Server. So, we used Upsize Wizard to migrate our tables to SqlServer's tables. It was successful.
But for some users i wanted to grant read-only permissions for all data. Before the migrating i used to open Access app. with a script and write "/ro" - that gave only read-only permissions. But now user still can go inside the linked tables and write there! How can i prevent this from a script? Thanks in advance,

IMHO the upsizing wizard is great for getting your tables etc to the server but thats only part of the overall picture. SQL Server is a whole different beast to Access you are dealing with data stored outside of the curtilage of Access and thus need to consider permissions being controlled and dealt with on the SQL server side. This is typically dealt with using SQL Servers enterprise manager/query analyser (if SQL 2000) or management studio if using SQL Server 2005. The direction you need to take is to look at creating a ROLE on the SQL server and adding your users to the role that has specific permissions to your database tables/objects.

From an MS Access perspective you have two options for interacting with the data the MDB file........ or the ADP project (which exposes stored procedures in the GUI and in which you can write your scripts to control relevant permissions and groups of permissions as an administrator for tables views and so on)
Doing this within an MDB file will require VBA code to write functions that connect to the server via a DSN communicating that way and ultimately creating users and groups and permissions and so on.

Hope this helps

Jim :)

[ADezii]

Hello.
I have the following problem. In our project we decided to use Access as link to SQL Server. So, we used Upsize Wizard to migrate our tables to SqlServer's tables. It was successful.
But for some users i wanted to grant read-only permissions for all data. Before the migrating i used to open Access app. with a script and write "/ro" - that gave only read-only permissions. But now user still can go inside the linked tables and write there! How can i prevent this from a script? Thanks in advance,

Jim is 100% correct in what he states. All Permissions for data should be controlled from SQL Server, not Access, and in my opinion it is rarely a good idea to Open an Access Application as Read Only using the /ro Argument.

[sxam]

Thanks everybody for answering.

The thing is that i don't want my users to feel any change. What happened before was that user could click on two different scripts. One that opened an MDB file the usual way (for editing data) and one that was used 90% of the cases - for looking at data (with /ro). It was done to ensure lack of mistakes - it often happened that user (with permissions) edited the data with no intention to do it.
So now, after we transfered the data to SQL Server, what i wanna do is exactly the same. Same user clicks on one script, and that script will open data for editing. Second script will open data for Read-Only access.

Is that possible to open Access application through a script and to tell it to connect to SQL Server (where the data is) using different users?

[Jim Doherty]

Thanks everybody for answering.

The thing is that i don't want my users to feel any change. What happened before was that user could click on two different scripts. One that opened an MDB file the usual way (for editing data) and one that was used 90% of the cases - for looking at data (with /ro). It was done to ensure lack of mistakes - it often happened that user (with permissions) edited the data with no intention to do it.
So now, after we transfered the data to SQL Server, what i wanna do is exactly the same. Same user clicks on one script, and that script will open data for editing. Second script will open data for Read-Only access.

Is that possible to open Access application through a script and to tell it to connect to SQL Server (where the data is) using different users?

Hi sxam,

If you are going to use SQL server you need to understand client server behaviour. data is stored 'outside' of Access. You have given your users a 'big' change there so it will not work exactly the same as it did before.

The command line switches were primarily designed for objects running in the JET database environment ie localised mdb tables not SQL server tables in other words if you use it with attached tables from SQL server in your mdb database then any local mdb tables will be read only....yes..... but not SQL server tables because like I said before, users and permissions are controlled on the SQL server in its own environment. Microsoft Access merely connects to the server. You need to look at server side permissions aligned to identification of 'who' is logging in using (I would suggest) windows integrated security. Your users must exist on the server and have appropriate permissions set there.

Documented help is less than clear on this in fairness to you on the /ro switch but you can gain a flavour of of what I mean if you look at it within the context of the 'switch' that deals with compacting for instance. Your local mdb file server database is 'compacted' but not the SQL server database. Does this make sense to you?

Regards

Jim :)