Create a view that passes windows authentication as a criteria

[MICKI0220]

I am trying to restrict what a user can see and edit on an access form and reports. My front end is Access and the data is stored in SQL 2008. I was thinking that I could make the view restrict what is seen by using windows authentication in the criteria. Basically, my users are supposed to maintain a log that only they can see and the managers. This info is filtered by their user name. I want to assign a view the users but its parameter is their name based on authentication. I hope I am making sense.
I have a table that has their full name and then their windows id. How can I do this. Any help is appreciated.

SELECT     dbo.DATA.*, dbo.Names.WindowsId
FROM         dbo.DATA INNER JOIN
                      dbo.Names ON dbo.DATA.WorkName = dbo.Names.NameOfUser

 

[Qik3Coder]

Seems like the only thing you are missing is

AND dbo.Names.ADUserName = suser_sname()

Note this only works if the users have direct access to the database, which if they do means you need to go ask how to fix that in the SQL Admin forum.

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.

 

[MICKI0220]

I am not sure what "ADuserName" is or what it relates to but here is the new code. Does this look like what I am requesting. I will have to do some testing

[code
SELECT dbo.DATA.ID, dbo.DATA.Date, dbo.DATA.[Job #], dbo.DATA.[Project Name], dbo.DATA.WorkName, dbo.DATA.Title, dbo.DATA.Description, dbo.DATA.[Non-Billable Time],
dbo.DATA.[Billable Time], dbo.DATA.[Out of Office], dbo.DATA.[Type of Non-Billable], dbo.DATA.Test, dbo.Names.WindowsId
FROM dbo.DATA INNER JOIN
dbo.Names ON dbo.DATA.WorkName = dbo.Names.NameOfUser AND dbo.Names.WindowsId = SUSER_SNAME()
[/code]

 

[Qik3Coder]

You need to compare whatever field holds their active directory name to the suser_sname() function.
be careful, as this script may work correctly in SSMS (Query Analyzer) but fail to work from your app. This will be because you use a dedicated SQL User to connect to the database, which will require you to get the ADName and pass it into the stored procedure.

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.