how to discover either windows group or SQL login

[TimG]

Hello,

I am putting together a row level security plan for our sales
database. I will give a brief description of the method I am thinking
of using to give you an idea of how I will need to be able to discover
the group or login the user is using to access the data.

I have a table called salesfact, it has all the sales info for all the
branches of our company. Each order(row) that is inserted has an entry
in the division_number column to describe which branch the order
belongs to. I have created another table(Branch_Folks) that has four
columns; username, windows_group, SQL_Login and division_number.

I am using a view and SQL logins to control access to the data based
on the user, the SQL logins give windows groups access to the view.
Only users that are added to the specific branch groups will have
access to the logins, but if a user is added to the group without me
being notified, then he will not have a corresponding entry in the
Branch_Folks table. Currently I am using the SUSER_SNAME() function to
determine which user is accessing the data from the view that I have
created. The view uses this select statement to filter the data based
on the user & division.

Select * from tsalesfact A, Branch_Folks B where SUSER_SNAME() =
B.username and A.division_number = b.division_number

This method works fine, but I will have to manually maintain the user
list in the Branch_Folks table in case a new user joins the windows
branch group. I would like to use a function similar to SUSER_SNAME()
that can determine the windows group or SQL Login a user is using.

Does anyone know of a way to do this??

Thanks a ton,

Tim

[Erland Sommarskog]

[posted and mailed, please reply in news]

TimG (ti****@hotmail.com) writes:

This method works fine, but I will have to manually maintain the user
list in the Branch_Folks table in case a new user joins the windows
branch group. I would like to use a function similar to SUSER_SNAME()
that can determine the windows group or SQL Login a user is using.

Have you looked at is_member()? If I understand you correctly, this
seems to be the function you are looking for.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp