Domain Group Members and Database Roles

Greetings...
I have a SQL2005 Express database where I have created 3 user defined
database roles, granted those db roles all the required permissions on the
required db objects, and made a few (Domain\Group) groups login members of
those database roles. The 3 roles are for high, medium, and low rights.
When a user logins with Windows NT authority, I can determine their identity
with
SUSER_SNAME(), but how can I easily determine what role or roles the user,
not the group, belongs to? The user would not have a principal_id, but the
group does. There's got to be a way to know what role the USER is in, isn't
there?
DaveHi
Try
sys.database_role_members catalog view
"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
> Greetings...
> I have a SQL2005 Express database where I have created 3 user defined
> database roles, granted those db roles all the required permissions on the
> required db objects, and made a few (Domain\Group) groups login members of
> those database roles. The 3 roles are for high, medium, and low rights.
> When a user logins with Windows NT authority, I can determine their
> identity
> with
> SUSER_SNAME(), but how can I easily determine what role or roles the user,
> not the group, belongs to? The user would not have a principal_id, but the
> group does. There's got to be a way to know what role the USER is in,
> isn't
> there?
> Dave|||Uri,
that view is not too informative... I finally got this query to work for
what I want:
Select name as Roles
From sys.database_principals
Where type='R' And is_fixed_role=0 And principal_id<>0
But I wouldn't call this 'easy' or intuitive...
Dave
"Uri Dimant" wrote:

> Hi
> Try
> sys.database_role_members catalog view
>
> "DGardner" <DGardner@.discussions.microsoft.com> wrote in message
> news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
>
>|||Hi Dave.
Try with the IS_MEMBER function
Regards
Antonio Soto
Solid Quality Learning
http://www.sqlu.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"DGardner" <DGardner@.discussions.microsoft.com> escribi en el mensaje
news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
> Greetings...
> I have a SQL2005 Express database where I have created 3 user defined
> database roles, granted those db roles all the required permissions on the
> required db objects, and made a few (Domain\Group) groups login members of
> those database roles. The 3 roles are for high, medium, and low rights.
> When a user logins with Windows NT authority, I can determine their
> identity
> with
> SUSER_SNAME(), but how can I easily determine what role or roles the user,
> not the group, belongs to? The user would not have a principal_id, but the
> group does. There's got to be a way to know what role the USER is in,
> isn't
> there?
> Dave|||Antonio,
Sorry... IS_MEMBER() doesn't work either. Although the group is assigned to
the role, the individual user who is a member of the group is not, and so
IS_MEMBER() returns 0 every time. It looks like the query I constructed is
the only way to get the info I want.
Dave
"Antonio Soto" wrote:

> Hi Dave.
> Try with the IS_MEMBER function
> Regards
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "DGardner" <DGardner@.discussions.microsoft.com> escribió en el mensaje
> news:2BE1DB54-0A86-4CF8-A916-0666AD0790AF@.microsoft.com...
>
>