Access to SQL

[seanog2001]

Hi all,

I have an access database on SQL Server 2005 which users can access via the server. I want to be able to restrict different users to different Forms/GUI's, how would i be able to do this?

[Jim Doherty]

Let us assume you have created ROLES on the server. Roles having the names of Administrator,Writer and Reader with permissions set relevant to each role.

Your users are created on the server and added to the relevant roles so that they inherit the permissions for the role. What you then need is to pass this logical structure back to your application frontend, so that you have a yardstick to control the features in your app that the user has access to or NOT.

You might think it appropriate to use your frontend main menu form for this by setting its recordsource to a simple passthrough query that calls to the server to return the system_user and the relevant role to which they are allocated. Each of these pieces of data could then be mounted on the frontend form and used by you to control opening various forms or reports or any other logic you deem necessary.

Create the following user defined function on your SQL Server.

 
CREATE FUNCTION dbo.UDF_UserMembership
( @fuser varchar(100) )
RETURNS varchar(100)
AS
BEGIN
RETURN CASE 
WHEN @fuser IS NULL THEN 'Not known'
WHEN is_member('db_owner')=1 THEN ' DBOwner'
WHEN is_member('Administrator')=1 THEN 'Administrator'
WHEN is_member('Writer')=1 THEN 'Read Write'
WHEN is_member('Reader')=1 THEN 'Read Only'
ELSE 'Not established'
END
END

Now create a simple stored procedure that uses an SQL SELECT on a table in that database. You will see here a reference to a table called Usys_tblConfigLocal. You can either create that or use any table name you like it really does not matter as nothing is 'actually' being selected from the particular table itself, it is merely a reference to satisfy the FROM clause of the select statement.

 
CREATE Procedure dbo.usp_MainMenu
AS
SELECT
system_user as SystemUser,
dbo.UDF_Admin_UserMembership(system_user) as Membership
from dbo.Usys_tblConfigLocal
return
GO

The above procedure calls back to your application a one row two column dataset. You can call this procedure by creating a simple passthrough query, save it with whatever name query name you like you like but paste the following simple one line into the SQL query window of the passthrough itself:-

dbo.usp_MainMenu

You now simply use this passthrough query as the recordsource for your main menu and mount two textboxes on screen, and there you have it, a relatively simple method of determining at your 'frontend' which SQL server group a user belongs to and hence what you will alow them to do in your app.

 
IF Forms!MainMenu!MemberShip="Read Write" THEN
'open this form
ELSE
'open that form
END IF

[pkstormy]

Here's what I do:

http://www.dbforums.com/6332819-post68.html

It grabs the loginID automatically and looks at a security type table of the users to determine their security level and then to make visible/invisible/enabled/disabled the buttons on the form.