| Author |
Topic |
|
Andrew Parsons
Starting Member
7 Posts |
 Posted - 2010-11-29 : 15:50:17
|
| Hello all.Sql express 2008 sp 2Access 2010Database uses odbc linked tables to the sql server.Windows NT loginsFirst let me thank you for trying to help.I have built a database where people in the field submit to the SQL server via Access 2010. Everything is working great, but I am looking for a slight change to it.Each user from the field submits to a series of different tables. Each record they submit to each table has a field with their Windows NT login inn it. What I want to do is somehow make it so each user can only see their own records in each table based on the Windows NT login field. Of course some user will still need to see all the records. I know I can use filters in Access 2010 for this however I do not want the user to have the option to remove the filter and thus be able to see all the records.Table information:Table [DailyReport] Windows NT login field in table [DailyReport] is [FluidControlLogin]Sorry if i am doing a horrid job of explaining this, so please if you need any more info please let me know.Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Andrew Parsons
Starting Member
7 Posts |
Posted - 2010-11-29 : 17:10:14
|
| How do i do that sql side?I wuld think somthing like this:SELECT [FluidControlLogin] FROM [DailyReport] WHERE [FluidControlLogin] = SYSTEM_USER()But how do i make it do this to every table and to user i select?Thanks |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Andrew Parsons
Starting Member
7 Posts |
Posted - 2010-11-29 : 17:14:48
|
| Does SYSTEM_USER() provide the correct login?FluidControlLogin has nt style login DOMAIN\USERNAMEHow do i check SYSTEM_USER() and if id does provide the correct login? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Andrew Parsons
Starting Member
7 Posts |
Posted - 2010-11-29 : 17:25:19
|
quote:
Originally posted by tkizer
Run in SSMS: select SYSTEM_USER
I am in SSMS, do i run it in a query or do I run a stored procedure? and how? I am so sorry I am a newb, well not that bad but close enough.Thanks Andrew |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Andrew Parsons
Starting Member
7 Posts |
Posted - 2010-11-29 : 17:42:45
|
quote:
Originally posted by tkizer
SSMS is going to show your user. You need to test your client users. If they have SSMS, you can run it in a new query window.
Okay so i found it and it does show domain\username under Name in sysusers.Thanks thus far |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Andrew Parsons
Starting Member
7 Posts |
Posted - 2010-11-29 : 18:04:00
|
| I understand what needs to be done, just need a tad more detail on how to throw views on tables in sql. I understand how to reference the views in Access. thank youAndrew |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Andrew Parsons
Starting Member
7 Posts |
Posted - 2010-11-30 : 09:58:06
|
| Thank you very much. NOW can I limit thier permmision in the master table where the view was created from?Thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Create procedure so user can only see his records