I'm using sql server 2005 with windows authentication and I have a
table tblEmployee
now I want to add a field called homeAddress varchar(200), but I want
to set its permissions so that only
the employee
his/her manager
the HR group

can view/edit the data

I'm using access2007 with a linked table to tblEmployee
how would I set the permissions in sql server ?
I'm thinking of a trigger, but how, within the trigger, do I capture
'who is trying to access the data', ie the windows user running the ms-
access application ?

You can set a group policy in the active directory of the windows server
and then set sql server permissions for that group - add the names of
the desired users to this group, then that group inherits the
permissions set at the sql server. Or you can set individual
permissions on the sql server per user. I think you are thinking of the
groups. That is done at the windows server level. Don't forget - it's
all Microsoft, and it is all integrated.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Roger wrote:
This is a large topic, so start by reading this article called
"Implementing Row- and Cell-Level Security in Classified Databases Using
SQL Server 2005":
http://technet.microsoft.com/en-us/l.../cc966395.aspx

As long as the linked table is connecting to sql server with integrated
security, the windows user is always known to the database engine. The
system function SUSER_SNAME() will return the login name of the
currently logged in user.

The following will need to be true:
1. The windows user name is stored in the employee's record.
2. Your database includes the data to identify the login name of the
manager of each employee
3. Your database includes the data to identify the login names of the
members of the HR group


--
HTH,
Bob Barrows

On Oct 12, 9:03*am, Rich P <rpng... (AT) aol (DOT) com> wrote:
yes, I've already tested the group creation, thus the HR group, so I
know that works
but I don't want you to see my homeAddress, just yours
so Bob's SUSER_SNAME() in a trigger should work, I just have to
review his link

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On the FE, why not adjust the form or report query to show only information
available to the user? That way they will not get error messages about the
special table/row/column permissions setup in SQL server.

On Oct 12, 9:43*am, "paii, Ron" <n... (AT) no (DOT) com> wrote:
because smart users can link tables to another mdb and see the data
so permission must done on sql server at the table / field level
my employee table already stores the windows login, SUSER_SNAME()
should work
I just need to test it all

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Oct 12, 9:43 am, "paii, Ron" <n... (AT) no (DOT) com> wrote:
You may have other security issues if someone can link to SQL server with
any old copy of Access and see tables/rows/columns to which you have not
given them rights.

paii, Ron wrote:
Not true if permissions are set within the SQL Server.
--
HTH,
Bob Barrows

You need to create either more groups or give individuals permission to
each userID on the sql server

Rich

*** Sent via Developersdex http://www.developersdex.com ***

"Bob Barrows" <reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote

tables to another mdb and see the data". Which I assumed he has seen happen.