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
>> 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
>> AD group membership are not really visible to SQL Server at all. Can
>> 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,
>> 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
>> a member of the AD group that was granted access to the database. Once I
>> 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.
>> the user is a member of a particular DB role (or AD group) then they
>> 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
>> 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
>> variable. So I have to insert into a temporary table. Inserting into a
>> table means I can't do it in a function, because temp tables cannot be
>> 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
>> existing Access application so I'm constrained to passing the data to
>> via the existing linked tables. So I have to have the row-level filtering
>> 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
>> user has not been explicitly granted access to the DB but the group they
>> a member of has been)?