Re: Using SQL username as criteria on a view
From: Herbert (Herbert@aol.com)
Date: 05/30/02
- Next message: Dan Guzman: "Re: users with no logins"
- Previous message: Jeremy Knox: "New to SQL server"
- In reply to: BP Margolin: "Re: Using SQL username as criteria on a view"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Herbert" <Herbert@aol.com> Date: Thu, 30 May 2002 13:09:24 +0100
You can find out the login name of the logged in user running the query by
using
select suser_sname(suser_sid())
Note that this may return Domain\Username for a windows login or a sql
server login name (depending which one the user has logged in with).
You could therefore go one step further and use something like:
select Invoice
from States
where State in (select [state to view] from [allowed states] where
username=suser_sname(suser_sid()))
or
select states.state, states.invoice
from states inner join [allowed states]
on states.state=[allowed states].[state to view]
where [allowed states].username=suser_sname(suser_sid())
HTH
"BP Margolin" <bpmargo@attglobal.net> wrote in message
news:#SQjFH0BCHA.2296@tkmsftngp05...
> Duncan,
>
> Try something like:
>
> select ...
> from ...
> where (Username = 'BOB' and States in ('MT', 'VA'))
> or (Username = 'SALLY' and States in ('SC', 'DE'))
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "Duncan Isley" <divi@mindspring.com> wrote in message
> news:943101c20721$fdbe5660$3aef2ecf@TKMSFTNGXA09...
> > Ex)
> > SQL username: BOB
> > View called STATES, with fields of STATE and INVOICE
> >
> > Question,
> > If this user is logging in via ODBC (MS Access) and I want
> > him to only see records from the STATES view of MT and VA,
> > but have the criteria driven off of the SQL LOGINNAME
> > mapped thru (such that this crossmap table filters the
> > records)
> >
> > Username STATES TO VIEW
> > BOB MT
> > BOB VA
> > SALLY SC
> > SALLY DE
> >
> > Such that I would only create 1 view, but have this login
> > table handle the records s/he could query against.
> >
> > Thanks,
> >
> > Duncan
>
>
- Next message: Dan Guzman: "Re: users with no logins"
- Previous message: Jeremy Knox: "New to SQL server"
- In reply to: BP Margolin: "Re: Using SQL username as criteria on a view"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
