How to prevent deleting SQL tables thru FE Application?

how about using disconnected ADO recordset...

Set the security properly in the SQL Database.

 I'm assuming here you mean a regular user and not a developer.

Jim.

@Ray,
Dont think this is is an option for ADP.

@Jim,

Set the security properly in the SQL Database.

I had that setup once, and created a user in SQL w/o delete objects permission, however due to certain issue (dont recall what issue was, I think some UDF's or SP didnt function properly) I had to give sa rights to users.

..a regular user and not a developer

I mean both, as developers if need to delete something they can do it in SSMS, no need Access FE for such actions.

Thanks,
Ben

Ben,

<<I had to give sa rights to users.>>

 well...you've got what you've got then.

Jim.

I had that setup once, and created a user in SQL w/o delete objects permission, however due to certain issue (dont recall what issue was, I think some UDF's or SP didnt function properly) I had to give sa rights to users.

You answered your own question.
Listen to Jim. No problem will be solved when you give up.

/gustav

Hi Bit,

Thanks very much for this nice write-up..its always a pleasure to read your comments (lectures..:)

At the moment when I change the connection setting to that user with read only permissions, I get the attached error (and I believe there are many such instances), which I assume its due to missing permissions, now my question

1- See attached permission/s I have assigned for AccessUsers, let me know if thats OK?
2- Is there a way to grant execution permission for all UDF's and SP to that user?
3- In case #2 its not possible, how can I get a list of all SP/UDF's that this user does not have access to?

Basically I'm only concerned about users not deleting objects, as for modify for the time being I myself cannot do it from Access, as we have a later version of SQL(2005) than of Access(2003).

Thanks,
Ben
untitled.png
Untitled.png

Hi Ben,

which error do you mean? It cannot be the dropbox error or did you upload the wrong screenshot?

Yes, you can give the "Execute" permission on the schema "dbo" (if you do not use other schemas, otherwise there). For UDFs it is "Execute" and "Select".

I have one schema for all basic objects (tables/views) and one for objects available for the frontend for SPs and UDFs so I can separate the permissions.

datareader and -writer are basic SQL Server roles which groups the permissions. You can simply create your own role and assign this role all detailed permissions to objects and then assign the user to this role. This allows you a more detailed separation of permissions than using the built-in roles. In general you also don't need roles (except public) if you have a general AccessUser login for all end users, you can then also assign all needed permissions directly to this user.

Modifying can be done also by someone with a higher Access version and also by users simply using the connection string for an own SSMS installation or by creating a direct connection with Excel or simply VBScript. Only because you cannot do it with your environment that doesn't mean other users will not try it on other ways if they find out they have the permission - especially sa permission.

Cheers,

Christian

Hi,

did you upload the wrong screenshot?

Right, see attached for correction.

if you have a general AccessUser login for all end users, you can then also assign all needed permissions directly to this user.

Yes thats the case, so how do I go ahead with this and assign execution rights to all database objects for this user, without having to do it for each object separately?

Thanks,
Ben
untitled.png

Hi Bit,

So it is no good idea to set that on the schema "dbo" as you would have the same as before (would also allow to delete objects).

So from what I understand, there is no way to give assign rights for schema dbo just for manipulating data only, without having rights to deleting/modifying objects, is that correct?

The standard roles "db_datareader" and "db_datawriter" are a good start to give general permissions on all tables which includes everything which Access needs to work with the database.

Right, for all tables in the database this works, however where I'm having problems is with UDF's and SP's, where as shown above, user 'AccessUsers' dont have access to some of them.

I was able to find a query that gives me a list of all functions/stored procedures that this user has been granted rights to.
https://ask.sqlservercentral.com/questions/44972/how-can-we-find-user-have-execute-right-on-sp-.html
Now my question is, how can I get the opposite, meaning a list of all functions that this user has not been granted permission?

Again, as what i understand from your post, there is no way just to tell SQL, something like "Grant execution to 'AccessUser' for all my stored procedures..", and I must do it individually, therefore I'm asking..

PS. If the answer to this question is complicated, I will close this post and start a new one on that topic.

Thanks,
Ben

Hi Bit,

From your link..

GRANT EXECUTE TO [MyDomain\MyUser]
That will grant permission at the database scope, which implicitly includes all stored procedures in all schemas. This means that you don't have to explicitly grant permissions per stored procedure.

Tested that on test database for user 'AccessUsers' and after running that its still doesn't let me modify objects, so that means is it OK for me to take this route in production DB?

Thanks,
Ben

Thanks very much for guiding me all this thru completion!

Hi Bit,

Just updating, I did apply that and Command(s) completed successfully. (see attached).
However this user still could not see all procedures, only after doing it individually do they work.
something like

GRANT EXECUTE ON dbo.procStatistics --(Sounds familiar-:)
    TO AccessUsers; 

Select allOpen in new window

Thanks,
Ben
Untitled.png

Hi Ben,

allowing to execute an SP is not the same as allowing the user to do what the SP wants to do. This is why I wrote about "EXECUTE AS" with a no-login-user above.

Moreover, "seeing" is also not the same as executing. You will not see the SP in the navigation bar of Access if you only grant execute permission and that's good, the user should not see it. So the application is the only one which can execute the SP already knowing it's name, it also cannot get information about it if it only has execute permission.
That also includes the Parameters.Refresh method of ADO, it will not get information about the SP and it's parameters. You must build the parameters on your own at the client. I've made some UDFs which retrieves information about the parameters so I don't need to give "view definition" permissions (which includes reading the contents of the SP if it is not encrypted) but have the possibility to download the parameter information, so I have my own "Parameters.Refresh".

Cheers,

Christian

Hi Bit,

I changed the login/pwd of the application from sa to 'AccessUsers' as suggested, and gave it for some users to test it, see attached.
Now they came back saying that the application is performing slower..does that make sense?!

Thanks,
Ben
Untitled.png

Hi Ben,

although the sa don't need to check any permissions as he has all I really doubt that the application is slower only by using another login user with less permissions.
It could also be a placebo effect, if you say to someone that something has changed some people think they now see a difference, especially in values which they can't really measure.

If I were you I would create two frontends, one with sa and one with AccessUser and then test (measure) both on your own to see if that's really the case.
If you find any difference then you can check everything what's happening in the front- and backend and see where the problems in performance can be found.

Cheers,

Christian

Hi Bit,

...some people think they now see a difference, especially in values which they can't really measure.

Yea I also thought so, and perhaps will try the following, will create two files like you suggested above, except that will not tell them anything and see if they realize a difference, and later see if they can tell me which is what..

Thanks,
Ben