X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Will My Users Need Access To The Sql Server Database?, Access 2010    
post Mar 22 2016, 11:08 AM

Posts: 2
Joined: 22-March 16

Hi All,

We have an Access 2010 .ADP database that is currently being used by the users to log their time spent on a specific activity. All the tables for this database reside in a MS SQL Server 2008 database.

There are separate tables in the SQL server database such as Roles, Users, Report List etc. The users tables will have a list of all the user id's that will be using this database. The Roles table will have a list of all the Roles that are assigned to specific User Id's and the report list will have Roles and the Report names that the users can choose from.

Once the database is opened by the user, The database automatically detects the user id and presents a set of dropdown options for the user to select from based on a "Role" assigned to that particular User id.

One of my users who is testing the database (I have not yet deployed the database to all the users) is complaining that she is getting an error message that states "The Select permission was denied on the object 'Report_List', database 'databasename', schema 'dbo' "

Do you know why the user is getting this error? When I open this database on my PC, I do not see this message. Will the users need "Read Only" access to the SQL server database in order to not see this error?

Go to the top of the page
post Mar 22 2016, 11:15 AM

Posts: 7,115
Joined: 22-December 10
From: England

I think it depends on how you've set up SQL server for access (as opposed to Access).

There are generally two choices' Windows authentication and SQL authentication.
What I've done with my early ones is to use SQL authentication, and then use a common user and password to get the FE to sign on to the BE.

The default is I think Windows authentication, so I suspect that you will have to go back and review users and IDs (Mind you. I've been using it for less than a week)

It depend really on whether your main current aim to let people in, or to keep them out.
Go to the top of the page
post Mar 22 2016, 11:39 AM

Posts: 2
Joined: 22-March 16


Thank you for the reply, Actually there is a group of users who are currently using this database without any issues.

I am wondering why they are not getting the error message as the new users because in SQL server under the database name and Security>Users, I do not see their User id's listed in there.
Go to the top of the page
post Mar 22 2016, 11:48 AM

Posts: 7,115
Joined: 22-December 10
From: England

Sorry. I've reached the end of my knowledge then.
BUT PLEASE when you do get this solved, share the answer.
GroverParkGeorge seems to be fairly well up on SQL server.
Go to the top of the page
post Mar 22 2016, 01:05 PM

UA Admin
Posts: 36,991
Joined: 20-June 02
From: Newcastle, WA

There are two levels of "user" here. One is the level in your user defined tables, i.e. the tables you created to manage the users, their roles, and their access to objects per your security plan.

The other is at the database level. These are Database Roles managed by SQL Server directly. It sounds like your users are being blocked at this level, despite any security access granted by your UD tables.

Here's a screen shot to help illustrate what I'm talking about.

Attached File  2016_03_22_10_51_48.png ( 13.29K )Number of downloads: 1

There is a database role called XXXAccessAppl for the database named srcXXXXXXX. This database role contains 1) a list of Windows Users who have privileges on this database, and 2) a list of Objects (tables, views, stored procedures, etc.) and the specific privileges they have on each object.
Attached File  2016_03_22_10_53_10.png ( 39.75K )Number of downloads: 1

This one shows the Objects in the database and the specific privileges the members of the role have, along with the Grantor of those privileges. Note that the dbo, or data base owner, has granted insert, delete and other privileges, not visible here.

Attached File  2016_03_22_10_54_01.png ( 34.91K )Number of downloads: 1

This one show role members. These are the Windows IDs of the specific people added to this database role. Individuals Do NOT get privileges based on that Windows ID; they get privileges because they are members of this group, and this GROUP has those privileges.

You can create database roles for each of your UD roles and assign the people you want to have the privileges of that role into that database role. Then, grant each database role privileges only on the objects (tables, views, stored procedures, etc.) that role should use.

I hope that's clear enough to get you started.

Despite Dave's kind words, I'm by no means the most informed SQL Server developer here and I'm sure there will be additional details forthcoming.
Go to the top of the page
post May 31 2016, 12:25 PM

Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>

In an .ADP, the credentials can be found on the FILE tab. Click the SERVER button and then choose CONNECTION.

If you have it set up to use a specific username, everyone using your .ADP uses these credentials. This account must have permission to the database on SQL Server.

If you have it set up to use Windows NT Integrated Security, each user will have to have access individually to the SQL Server database.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    6th April 2020 - 11:18 PM