It's a pretty frustrating situation where you call Is_Member in sql server 2005 to find if the current user belongs to a particular role, only to have it return 0 when that user is in fact a member. The reason for this is that when you log in as a member of the sysadmin role, you essentially become dbo which ignores all such role mappings.
To put it simply running the following as a dba,
select * from sys.user_token
would return one row - the dbo. Running it as a non dba user will return all the principals the user is mapped to.
To create a workaround for this, I've had to implement my own Udf that pulls directly from the sys.database_principals and sys.database_role_members tables to fetch which database roles a user belongs to. Please not that this does NOT map out which windows principals the user belongs to:
create function [Security].[Is_Member](@rolename varchar(255))
returns tinyint
as
begin
-- Check to see that the principal being queried exists
declare @roleExists bit
select @roleExists = 1 from sys.database_principals where name = @roleName
if @roleExists is null return null
-- See if the current user is a member of that principal
declare @isMember bit
set @isMember = 0
select
@isMember = 1
from
sys.database_principals u
join sys.database_role_members m on u.name = suser_name() and u.principal_id = m.member_principal_id
join sys.database_principals r on m.role_principal_id = r.principal_id and r.name = @rolename
return @isMember
end
The function merely creates a new Is_member Udf within my own security schema which goes off and checks the aforementioned principal tables ot see if the current user is allocated to that principal.
Update
Alright, after a bit of playing with the above script, I ran into a wall when part of my app needed to use is_member to take advantage of windows groups being allocated to sql groups. Because of this, I've modified the udf back to the regular behaviour of the is_member function, except changing the default return value for dbo users to return 1 rather than 0 to role membership as such:
ALTER function [Security].[Is_Member](@rolename varchar(255))
returns tinyint
as
begin
-- Check to see that the principal being queried exists
declare @roleExists bit
select @roleExists = 1 from sys.database_principals where name = @roleName
if @roleExists is null return null
-- Check to see if the user is dbo
if user = 'dbo' return 1
return is_member(@rolename)
end