Membership of DB Role when User Gains Access via Active Directory

Membership of DB Role when User Gains Access via Active Directory

Post by U2ltb25EZX » Wed, 15 Feb 2006 12:31:27


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
 
 
 

Membership of DB Role when User Gains Access via Active Directory

Post by Antonio So » Wed, 15 Feb 2006 20:19:27

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" < XXXX@XXXXX.COM > escribien el mensaje
>> 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