SQL Sever side query to filter for user ID

[homeguard]

I currently have a query in an access database that filters the records in a Sql Linked table for their user ID. So basically i use Environ("UserName") to filter for a field that contains their user name. I want to secure this and put it on the SQL Server so there is no way anyone can see any records other then the ones that are filtered for them. Does anyone know how to do this? I think it will be some SQL query.

I'm still learning sql server 2005.

Thanks.

 

[tehNellie]

If you use windows authentication on your SQL server and application you can use

SELECT suser_sname()

Which will return DOMAINNAME\username of the current user

or

SELECT REPLACE(suser_sname(), 'domainname\','')

if you want to remove the domain name from your results.

So you could use something along the lines of:

SELECT mt.acolumn,mt.bcolumn
FROM dbo.mytable AS mt
WHERE mt.owner = REPLACE(suser_sname(),'domainname\','')

this works for SQL Server 2000 and I'm making an assumption it hasn't changed for 2005.

 

[homeguard]

thats the thing I am using a standard SQL login to import these tables so from the sever login everyone looks the same. Does that make sense?

 

[tehNellie]

it does, you could use a stored procedure and pass the username to it, it's less secure and is open to abuse if someone can see what's being done:

CREATE PROCEDURE dbo.spTest
(
  @owner varchar(10)
)
AS
BEGIN
  SELECT mt.acolumn,mt.bcolumn
  FROM dbo.mytable AS mt
  WHERE mt.owner = @owner
END

From within your application code you'd need to use something along the lines of:

exec sptest environ("username")

Although I always use an Command object when calling stored procedures from code, ymmv.

Personally I would suggest that if you're moving off linked tables anyway that you look into using individual logins whether done locally on SQL server or using Windows Authentication, as you've already noticed, the big downside to using a generic login is that you've no real idea who is doing what and that generic login probably has a much higher level of access to the database than you'd perhaps want.

If you really want to get into it, here's a link to Microsoft's suggestion on implementing row level security in SQL server 2005:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

 

[homeguard]

hmm ok thank you for your help