Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...If there has ever been a justification needed for access to the net during working hours, just referring to this site should suffice. Fantastic!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

Create a view that passes windows authentication as a criteria

thread183-1688443
Read More Threads Like This One
MICKI0220 (IS/IT--Management)
17 Jul 12 9:57
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.


CODE

SELECT     dbo.DATA.*, dbo.Names.WindowsId
FROM         dbo.DATA INNER JOIN
                      dbo.Names ON dbo.DATA.WorkName = dbo.Names.NameOfUser
Qik3Coder (Programmer)
17 Jul 12 16:26
Seems like the only thing you are missing is

CODE

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 (IS/IT--Management)
18 Jul 12 11:12
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 --> #

, 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()
Qik3Coder (Programmer)
18 Jul 12 15:31
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.

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close