Re: Membership of DB Role when User Gains Access via Active Directory
- From: "Antonio Soto" <antoniosotorodriguez@xxxxxxxxx>
- Date: Tue, 14 Feb 2006 12:19:27 +0100
Hi Simon,
SUSER_SNAME returns the Windows user name but USER_NAME should returns the
database user name, although you are logged through a Windows group.
did you try with IS_MEMBER function??
"SimonDev" <SimonDev@xxxxxxxxxxxxxxxxxxxxxxxxx> escribió en el mensaje
news:D101A521-0362-42F7-81D7-B48AFBB2E276@xxxxxxxxxxxxxxxx
Hi
I am trying to determine what DB role a user is a member of in SQL Server
2000. Users connect to the SQL Server via a trusted connection. They are
granted login permissions on the SQL Server and access to the DB via the
Active Directory groups they are members of.
As far as I can tell, users that have been granted access to a DB via
their
AD group membership are not really visible to SQL Server at all. Can
anyone
help?
sp_helpuser is no good, it doesn't recognise the Windows login of the user
as a user in the DB, only the name of the AD group they are a part of.
sp_helprolemember indicates that the AD group is a member of the role, but
not the user who is a member of the AD group. SYSTEM_USER, SESSION_USER,
CURRENT_USER, USER and USER_NAME() all return the user's Windows login,
not
the user in the DB they should be aliased to.
I was able to use xp_logininfo with SYSTEM_USER to determine if the user
was
a member of the AD group that was granted access to the database. Once I
had
the AD group name I could determine what DB roles it was a member of.
However, there is still a problem.
I'm trying to implement row-level security on various tables in the DB.
If
the user is a member of a particular DB role (or AD group) then they
should
only be able to see data for certain branches, based on a table in another
database that lists the branches each Windows user is a member of. If the
user is a member of another role they should be able to see all rows in
the
tables.
I have tried to implement this row-level security in views on top of the
relevant tables. Here's the problem: To get the information from
xp_logininfo I need to insert the results of the stored procedure into a
table. I can't insert the results into a table variable as I get an error
message saying you can't insert the output of an EXEC statement into a
table
variable. So I have to insert into a temporary table. Inserting into a
temp
table means I can't do it in a function, because temp tables cannot be
used
in functions. So I have to use a stored procedure. But I can't use the
output of a stored procedure in a view. The front-end to this database is
an
existing Access application so I'm constrained to passing the data to
Access
via the existing linked tables. So I have to have the row-level filtering
in
a view.
Does anyone have any ideas of how I can find what DB roles a user is a
member of, if that user only has access to the DB via their AD group (ie
the
user has not been explicitly granted access to the DB but the group they
are
a member of has been)?
--
Cheers
Si
.
- Follow-Ups:
- Prev by Date: Re: 'Domain\LocalServer$' is not a valid user
- Next by Date: Re: Who is using MSMQ?
- Previous by thread: SQL 2005 - audit SELECT statements?
- Next by thread: Re: Membership of DB Role when User Gains Access via Active Direct
- Index(es):
Relevant Pages
|
|
