.adp requires Sys Admin server role?
-
I am new to .adp files. The system seems to require that the group be given sys admin server role in order to use the file. If I take that role away (leaving them dbo for the db in question), it appends "(dbo)" to the end of each table name. Any suggestions?
-
This was removed by the editor as SPAM
-
I run a database hosting service, and we have people that use .ADP files even without db_owner permissions.
We grant our clients the following roles:
db_ddl_admin
db_accessadmin
db_datareader
db_datawriter
db_securityadmin
To be honest, I'm simplifying a little but the above fixed database roles do represent the user's permissions accurately. These users are able to use .ADP files.
The worst thing that happens is that they sometimes see a message that states "You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own." Notice the word "might" in the message, however. The users actually are able to save changes. The message is just an annoyance; they click OK and then get on with their work.
In fact, I just tested a scenario where I was able to connect to a database using an account that was only a member of the public role in that database, plus it had been given permission to a single table. Connecting through an .ADP, the user could see and use the single table. The table was named t1 (remember, this is just a test!) and was owned by dbo, so the .ADP was displaying its name (correctly) as t1 (dbo). This just means that it is the table t1, owned by dbo - owner names are displayed in an .ADP file when the object is not owned by the current user.
By the way, the .ADP also gracefully did not show the user any other tables that they did not have permissions for. This is a nice touch also.
Hope this helps,
Chris
-
Thanks fo ryour reply, Chris. It does help as these are database specific roles/permissions rather than server wide roles/permissions.
Can you recommend any resources for ADP's? We switched to this approach at the last minute, so I'm scrambling a bit.
-
I'm afraid I don't really know any specific resources for ADPs. Maybe somebody else can chime in on that.
> It does help as these are database specific roles/permissions
Just to reiterate, I don't even have to add those fixed database roles to give people access to their database through an ADP. I tested it with someone who had no roles except for public and everything worked as expected.
All we do with ADPs is to show departmental users how to use them as a means of accessing their data directly (without going through their front-end apps). Developers are the primary ones that use them, so that they can populate test data and whatnot. I think that there are better ways to do everything that our folks do through the ADPs, but for some of them, having that Access interface really helps them feel "at home."
I will mention one issue that comes up here from time to time. When people use ADP files that are stored on the network, the database access can be fairly slow. I can imagine why this must be, but haven't tested / traced anything, so I think I'd better keep my theories to myself. What I can say is that this might be due to congestion on our network ... or it might not. Right now, I just work around the problem by making a local copy of the ADP files I use in my %TEMP% directory.
Good luck! Hope you don't have to scramble too much.
Chris
-
When on the network, our adp is read only for all users except the first one to open it, so we are deploying to the local drive when we get the bugs worked out. Have you found a way around the network read only issue? If so, I'd love to hear it.
-
The .ADP itself can be opened for editing by only one user. This means that only one user at a time can post new forms, macros, reports, etc.
However, that's not the same as saying that the *data* is read-only. Since the data is stored in SQL Server (not in the .ADP), you can still use the shared .ADP (opened in read-only mode) to update records on the SQL Server.
Cheers,
Chris
-
I have created an .ade file, and it is on the network. As such, reports, forms, etc., can't be opened for editing. The users are strictly accessing data, and each user after the first gets the read only message.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply