Limit data to user"s own

Our intranet uses a SQL server.

I was wondering if I could write a view that would return the users data
only?

For example if Joe Blow opens the view, it only returns the records that the
"EmployeeID" field
matches his id

I have a table that contains the user's login name, and I'm using Windows
Authentication on the site and for SQL Authentication.

I could certainly send the Username as a parameter but was thinking that SQL
has the information anyway (from the connection) couldn't it somehow do it?

Thanks
Tom B

Re: Limit data to user"s own

Tom B wrote:
> Our intranet uses a SQL server.
>
> I was wondering if I could write a view that would return the users
> data only?
>
> For example if Joe Blow opens the view, it only returns the records
> that the "EmployeeID" field
> matches his id
>
> I have a table that contains the user's login name, and I'm using
> Windows Authentication on the site and for SQL Authentication.
>
> I could certainly send the Username as a parameter but was thinking
> that SQL has the information anyway (from the connection) couldn't it
> somehow do it?
>
> Thanks
> Tom B

You can create a view that filters on the CURRENT_USER function:

.... WHERE EmployeeID=CURRENT_USER

HTH,
Bob Barrows
PS. This answer applies to SQL 2000. If you are using an earlier version,
you can look up the system functions in BOL.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Limit data to user"s own

Thanks Bob.
That made sense. However for my account it's returning dbo.

I checked my connection settings and they are using SSPI.

I ran it in QUery Analyzer and it also returned dbo.

I checked Profiler and it shows the NTUserName as my name.

Yes, it's SQL Server 2000 (I forget that you don't know that ;) )

TomB

"Bob Barrows [MVP]" wrote in message
news:%23%23pqJJFwFHA.4056@TK2MSFTNGP11.phx.gbl...
> Tom B wrote:
>> Our intranet uses a SQL server.
>>
>> I was wondering if I could write a view that would return the users
>> data only?
>>
>> For example if Joe Blow opens the view, it only returns the records
>> that the "EmployeeID" field
>> matches his id
>>
>> I have a table that contains the user's login name, and I'm using
>> Windows Authentication on the site and for SQL Authentication.
>>
>> I could certainly send the Username as a parameter but was thinking
>> that SQL has the information anyway (from the connection) couldn't it
>> somehow do it?
>>
>> Thanks
>> Tom B
>
> You can create a view that filters on the CURRENT_USER function:
>
> ... WHERE EmployeeID=CURRENT_USER
>
> HTH,
> Bob Barrows
> PS. This answer applies to SQL 2000. If you are using an earlier version,
> you can look up the system functions in BOL.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Limit data to user"s own

Your account is "aliased" to dbo so that's what will be returned by
CURRENT_USER. For a normal user, their NT User Name should be returned by
that function.

Use SUSER_SNAME() instead of CURRENT_USER.

Tom B wrote:
> Thanks Bob.
> That made sense. However for my account it's returning dbo.
>
> I checked my connection settings and they are using SSPI.
>
> I ran it in QUery Analyzer and it also returned dbo.
>
> I checked Profiler and it shows the NTUserName as my name.
>
> Yes, it's SQL Server 2000 (I forget that you don't know that ;) )
>
> TomB
>
> "Bob Barrows [MVP]" wrote in message
> news:%23%23pqJJFwFHA.4056@TK2MSFTNGP11.phx.gbl...
>> Tom B wrote:
>>> Our intranet uses a SQL server.
>>>
>>> I was wondering if I could write a view that would return the users
>>> data only?
>>>
>>> For example if Joe Blow opens the view, it only returns the records
>>> that the "EmployeeID" field
>>> matches his id
>>>
>>> I have a table that contains the user's login name, and I'm using
>>> Windows Authentication on the site and for SQL Authentication.
>>>
>>> I could certainly send the Username as a parameter but was thinking
>>> that SQL has the information anyway (from the connection) couldn't
>>> it somehow do it?
>>>
>>> Thanks
>>> Tom B
>>
>> You can create a view that filters on the CURRENT_USER function:
>>
>> ... WHERE EmployeeID=CURRENT_USER
>>
>> HTH,
>> Bob Barrows
>> PS. This answer applies to SQL 2000. If you are using an earlier
>> version, you can look up the system functions in BOL.
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Limit data to user"s own

That's great.

Thanks for the help.

"Bob Barrows [MVP]" wrote in message
news:OSj26cHwFHA.3000@TK2MSFTNGP12.phx.gbl...
> Your account is "aliased" to dbo so that's what will be returned by
> CURRENT_USER. For a normal user, their NT User Name should be returned by
> that function.
>
> Use SUSER_SNAME() instead of CURRENT_USER.
>
> Tom B wrote:
>> Thanks Bob.
>> That made sense. However for my account it's returning dbo.
>>
>> I checked my connection settings and they are using SSPI.
>>
>> I ran it in QUery Analyzer and it also returned dbo.
>>
>> I checked Profiler and it shows the NTUserName as my name.
>>
>> Yes, it's SQL Server 2000 (I forget that you don't know that ;) )
>>
>> TomB
>>
>> "Bob Barrows [MVP]" wrote in message
>> news:%23%23pqJJFwFHA.4056@TK2MSFTNGP11.phx.gbl...
>>> Tom B wrote:
>>>> Our intranet uses a SQL server.
>>>>
>>>> I was wondering if I could write a view that would return the users
>>>> data only?
>>>>
>>>> For example if Joe Blow opens the view, it only returns the records
>>>> that the "EmployeeID" field
>>>> matches his id
>>>>
>>>> I have a table that contains the user's login name, and I'm using
>>>> Windows Authentication on the site and for SQL Authentication.
>>>>
>>>> I could certainly send the Username as a parameter but was thinking
>>>> that SQL has the information anyway (from the connection) couldn't
>>>> it somehow do it?
>>>>
>>>> Thanks
>>>> Tom B
>>>
>>> You can create a view that filters on the CURRENT_USER function:
>>>
>>> ... WHERE EmployeeID=CURRENT_USER
>>>
>>> HTH,
>>> Bob Barrows
>>> PS. This answer applies to SQL 2000. If you are using an earlier
>>> version, you can look up the system functions in BOL.
>>> --
>>> Microsoft MVP -- ASP/ASP.NET
>>> Please reply to the newsgroup. The email account listed in my From
>>> header is my spam trap, so I don't check it very often. You will get
>>> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>