SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Multiple users access to one table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anissw
Starting Member

USA
2 Posts

Posted - 10/21/2012 :  17:57:21  Show Profile  Reply with Quote
I have 18 single databases that are data entry applications for 18 sites in Microsoft Access. We are going to move to SQL Server 2008 and I have already imported the Access tables into one SQL table. In testing, I used the Access forms as the front end and attempted to login under different user login, however, data is displayed for all sites instead of based on the user site's login. How do I implement the data/tables/forms to display based on the user's login? Each site has about 3 people that will need to access one table, but needs to display their site's data and be able to conduct data entry.

Thank you

Neecy

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/21/2012 :  20:41:00  Show Profile  Reply with Quote
Depending on your needs, you may need to add one or two columns to your table in the SQL Server. If you just need to filter the data based on the login, you should add a column to indicate the login and store that information when data is stored.

When you store the data, depending on which client the data is coming from, you would populate this column. There are a number of built-in functions that will let you get the current user name - SYSTEM_USER for example.

When you query, you would query based on the login name as well (i.e., add a where clause that is something like WHERE loginname = SYSTEM_USER)

Edited by - sunitabeck on 10/21/2012 20:42:17
Go to Top of Page

anissw
Starting Member

USA
2 Posts

Posted - 10/22/2012 :  16:15:48  Show Profile  Reply with Quote
Thanks- I'll apply that and let you know the status.


Depending on your needs, you may need to add one or two columns to your table in the SQL Server. If you just need to filter the data based on the login, you should add a column to indicate the login and store that information when data is stored.

When you store the data, depending on which client the data is coming from, you would populate this column. There are a number of built-in functions that will let you get the current user name - SYSTEM_USER for example.

When you query, you would query based on the login name as well (i.e., add a where clause that is something like WHERE loginname = SYSTEM_USER)

[/quote]

Neecy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000