How to determine if a user implicitly belongs to a role

[t***@yahoo.co.uk]

Hi,

I need a function/procedure to be able to determine if a user belongs
to a role either directly or via another role.

E.g. if User X is in Role B and Role B is a member of Role A, I need
some way if determining that User X implicitly belongs to Role A as
well as Role B.

Internally Sql Server 2005 is able to do this somehow as when you look
under the members for Role A, User X is listed as a member because
User X is a member of Role B.

However, I can't find any system views, functions or procedures that
can determine this. Do I really have to write a procedure which will
recurs down through all the role members and sub roles?

Many thanks.

Tristan.

[Russell Fields]

Tristan,

If you are running in the context of the user that you want to know about,
just use IS_MEMBER and the SQL Server will determine if the membership
exists, even through deep nesting..

SELECT IS_MEMBER(Domain\WindowsGroupName)

If you are an administrator checking (and you are using SQL Server 2005 /
2008), you can:

EXECUTE AS LOGIN = 'Domain\Login'
SELECT IS_MEMBER ('Domain\GroupName')
REVERT

There was also an interesting discussion in this thread a few months ago.
http://www.developersdex.com/sql/message.asp?p=1925&r=6409772


RLF

Post by t***@yahoo.co.uk
Hi,
I need a function/procedure to be able to determine if a user belongs
to a role either directly or via another role.
E.g. if User X is in Role B and Role B is a member of Role A, I need
some way if determining that User X implicitly belongs to Role A as
well as Role B.
Internally Sql Server 2005 is able to do this somehow as when you look
under the members for Role A, User X is listed as a member because
User X is a member of Role B.
However, I can't find any system views, functions or procedures that
can determine this. Do I really have to write a procedure which will
recurs down through all the role members and sub roles?
Many thanks.
Tristan.

[t***@yahoo.co.uk]

Post by Russell Fields
Tristan,
If you are running in the context of the user that you want to know about,
just use IS_MEMBER and the SQL Server will determine if the membership
exists, even through deep nesting..
SELECT IS_MEMBER(Domain\WindowsGroupName)
If you are an administrator checking (and you are using SQL Server 2005 /
EXECUTE AS LOGIN = 'Domain\Login'
SELECT IS_MEMBER ('Domain\GroupName')
REVERT
There was also an interesting discussion in this thread a few months ago.http://www.developersdex.com/sql/message.asp?p=1925&r=6409772
RLF

I already tried using IS_MEMBER but it keeps returning 0 even through
the GUI shows I am member of the role.

I wrapped the call with EXECUTE AS but it still returned 0.

However - in the link you posted there is query that will acheive what
I am after - although I'd have to preferred to have used IS_MEMBER.

Thanks for the help.

Tristan.

[Russell Fields]

Tristan,

Well, I am glad that the code helped you, but I may have figured out why
IS_MEMBER does not work for you. This was definitely an "I did not know
that", so thanks for helping me learn.

In my test case, my domain\login is a user in the database also named
domain\login. So this masked what was happening for me. However:

-- Logins work for domain groups, but not for database roles
EXECUTE AS LOGIN = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT

-- Users work for both domain groups and for database roles
EXECUTE AS USER = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT

Then to clarify:
ALTER USER [domain\login] WITH NAME=[user]

-- User still works for both domain groups and for database roles
EXECUTE AS USER = 'user'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT

So, perhaps this will help you use IS_MEMBER

RLF

Post by t***@yahoo.co.uk

Post by Russell Fields
Tristan,
If you are running in the context of the user that you want to know about,
just use IS_MEMBER and the SQL Server will determine if the membership
exists, even through deep nesting..
SELECT IS_MEMBER(Domain\WindowsGroupName)
If you are an administrator checking (and you are using SQL Server 2005 /
EXECUTE AS LOGIN = 'Domain\Login'
SELECT IS_MEMBER ('Domain\GroupName')
REVERT
There was also an interesting discussion in this thread a few months
ago.http://www.developersdex.com/sql/message.asp?p=1925&r=6409772
RLF

I already tried using IS_MEMBER but it keeps returning 0 even through
the GUI shows I am member of the role.
I wrapped the call with EXECUTE AS but it still returned 0.
However - in the link you posted there is query that will acheive what
I am after - although I'd have to preferred to have used IS_MEMBER.
Thanks for the help.
Tristan.

[t***@yahoo.co.uk]

Post by Russell Fields
Tristan,
Well, I am glad that the code helped you, but I may have figured out why
IS_MEMBER does not work for you.  This was definitely an "I did not know
that", so thanks for helping me learn.
In my test case, my  domain\login is a user in the database also named
-- Logins work for domain groups, but not for database roles
EXECUTE AS LOGIN = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
-- Users work for both domain groups and for database roles
EXECUTE AS USER = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
ALTER USER [domain\login] WITH NAME=[user]
-- User still works for both domain groups and for database roles
EXECUTE AS USER = 'user'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
So, perhaps this will help you use IS_MEMBER
RLF

Post by t***@yahoo.co.uk

Post by Russell Fields
Tristan,
If you are running in the context of the user that you want to know about,
just use IS_MEMBER and the SQL Server will determine if the membership
exists, even through deep nesting..
SELECT IS_MEMBER(Domain\WindowsGroupName)
If you are an administrator checking (and you are using SQL Server 2005 /
EXECUTE AS LOGIN = 'Domain\Login'
SELECT IS_MEMBER ('Domain\GroupName')
REVERT
There was also an interesting discussion in this thread a few months
ago.http://www.developersdex.com/sql/message.asp?p=1925&r=6409772
RLF

I already tried using IS_MEMBER but it keeps returning 0 even through
the GUI shows I am member of the role.
I wrapped the call with EXECUTE AS but it still returned 0.
However - in the link you posted there is query that will acheive what
I am after - although I'd have to preferred to have used IS_MEMBER.
Thanks for the help.
Tristan.- Hide quoted text -

- Show quoted text -

Hi Russell,

Unfortunatley IS_MEMBER is still not working for me. I should have
mentioned that I am using SQL Authentication rather than Windows
Authentication so the Domain name is not an issue. The sql server I'm
connecting to only supports SQL Authentication.

Here's what I've just done using the GUI:

1. Created a new database name 'Test' - set owner to SA.
2. Added myself as a new user to this database which is linked to my
SQL Login on the Sql Server.
3. Created a new Role named 'TestRole' and added myself as a member of
it.

Then run:

EXECUTE AS LOGIN = 'Tristan'
SELECT IS_MEMBER('TestRole')
REVERT

0 is returned :-(

Tristan.

[t***@yahoo.co.uk]

Post by t***@yahoo.co.uk

Post by Russell Fields
Tristan,
Well, I am glad that the code helped you, but I may have figured out why
IS_MEMBER does not work for you.  This was definitely an "I did not know
that", so thanks for helping me learn.
In my test case, my  domain\login is a user in the database also named
-- Logins work for domain groups, but not for database roles
EXECUTE AS LOGIN = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
-- Users work for both domain groups and for database roles
EXECUTE AS USER = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
ALTER USER [domain\login] WITH NAME=[user]
-- User still works for both domain groups and for database roles
EXECUTE AS USER = 'user'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
So, perhaps this will help you use IS_MEMBER
RLF

Post by t***@yahoo.co.uk

Post by Russell Fields
Tristan,
If you are running in the context of the user that you want to know about,
just use IS_MEMBER and the SQL Server will determine if the membership
exists, even through deep nesting..
SELECT IS_MEMBER(Domain\WindowsGroupName)
If you are an administrator checking (and you are using SQL Server 2005 /
EXECUTE AS LOGIN = 'Domain\Login'
SELECT IS_MEMBER ('Domain\GroupName')
REVERT
There was also an interesting discussion in this thread a few months
ago.http://www.developersdex.com/sql/message.asp?p=1925&r=6409772
RLF

I already tried using IS_MEMBER but it keeps returning 0 even through
the GUI shows I am member of the role.
I wrapped the call with EXECUTE AS but it still returned 0.
However - in the link you posted there is query that will acheive what
I am after - although I'd have to preferred to have used IS_MEMBER.
Thanks for the help.
Tristan.- Hide quoted text -

- Show quoted text -

Hi Russell,
Unfortunatley IS_MEMBER is still not working for me. I should have
mentioned that I am using SQL Authentication rather than Windows
Authentication so the Domain name is not an issue. The sql server I'm
connecting to only supports SQL Authentication.
1. Created a new database name 'Test' - set owner to SA.
2. Added myself as a new user to this database which is linked to my
SQL Login on the Sql Server.
3. Created a new Role named 'TestRole' and added myself as a member of
it.
EXECUTE AS LOGIN = 'Tristan'
SELECT IS_MEMBER('TestRole')
REVERT
0 is returned :-(
Tristan.- Hide quoted text -
- Show quoted text -

I've just done a bit of reading and it looks like IS_MEMBER won't work
if you are in the sysadmin server role as your current user will be
dbo and not you.

Tristan.

[Russell Fields]

Ah, yes. That is true. So, you have to have a special case for the user
dbo along with the IS_MEMBER() case. E.g. either of the two below:

WHERE IS_MEMBER('ARole') = 1 OR IS_MEMBER('db_owner') = 1

WHERE IS_MEMBER('ARole') = 1 OR USER_NAME() = 'dbo'

RLF

Post by t***@yahoo.co.uk

Post by Russell Fields
Tristan,
Well, I am glad that the code helped you, but I may have figured out why
IS_MEMBER does not work for you. This was definitely an "I did not know
that", so thanks for helping me learn.
In my test case, my domain\login is a user in the database also named
-- Logins work for domain groups, but not for database roles
EXECUTE AS LOGIN = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
-- Users work for both domain groups and for database roles
EXECUTE AS USER = 'domain\login'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
ALTER USER [domain\login] WITH NAME=[user]
-- User still works for both domain groups and for database roles
EXECUTE AS USER = 'user'
SELECT IS_MEMBER('arole')
SELECT IS_MEMBER('domain\group')
REVERT
So, perhaps this will help you use IS_MEMBER
RLF

Post by t***@yahoo.co.uk

Post by Russell Fields
Tristan,
If you are running in the context of the user that you want to know about,
just use IS_MEMBER and the SQL Server will determine if the membership
exists, even through deep nesting..
SELECT IS_MEMBER(Domain\WindowsGroupName)
If you are an administrator checking (and you are using SQL Server 2005 /
EXECUTE AS LOGIN = 'Domain\Login'
SELECT IS_MEMBER ('Domain\GroupName')
REVERT
There was also an interesting discussion in this thread a few months
ago.http://www.developersdex.com/sql/message.asp?p=1925&r=6409772
RLF

I already tried using IS_MEMBER but it keeps returning 0 even through
the GUI shows I am member of the role.
I wrapped the call with EXECUTE AS but it still returned 0.
However - in the link you posted there is query that will acheive what
I am after - although I'd have to preferred to have used IS_MEMBER.
Thanks for the help.
Tristan.- Hide quoted text -

- Show quoted text -

Hi Russell,
Unfortunatley IS_MEMBER is still not working for me. I should have
mentioned that I am using SQL Authentication rather than Windows
Authentication so the Domain name is not an issue. The sql server I'm
connecting to only supports SQL Authentication.
1. Created a new database name 'Test' - set owner to SA.
2. Added myself as a new user to this database which is linked to my
SQL Login on the Sql Server.
3. Created a new Role named 'TestRole' and added myself as a member of
it.
EXECUTE AS LOGIN = 'Tristan'
SELECT IS_MEMBER('TestRole')
REVERT
0 is returned :-(
Tristan.- Hide quoted text -
- Show quoted text -

I've just done a bit of reading and it looks like IS_MEMBER won't work
if you are in the sysadmin server role as your current user will be
dbo and not you.

Tristan.