I am trying to get IS_MEMBER working in a UDF in Sql Server 2005.
I have a windows group "Domain\TestGroup".
I allocate my Login "Domain\Kieran" to it.
select SUSER_NAME(); gives "Domain\Kieran"
but select IS_MEMBER('Domain\TestGroup'); returns NULL.
Check your ERRORLOG and see if there is any error indicating that the SQL Server service cannot connect to the 'domain' Active Directory.
The NULL answer from IS_MEMBER means that SQL Server does not recognise the Windows group.
It looks at the login token from your connection. It does not query Active Directory.
From the BOL link:
IS_MEMBER determines Windows group membership by examining an access token that is created by Windows. The access token does not reflect changes in group membership that are made after a user connects to an instance of SQL Server.
So, even though doamin\kieran is in the group you'll probably need to log out and back in so your login token is updated with the group membership.
It should all work in your UDF (unless you have EXECUTE AS USER or OWNER in the UDF)
Ah. I think I see the problem.
My login is in builtin\Administrators which is a member of the server fixed role sysadmin.
Therefore I'm a member of sysadmin, which means I'm dbo and won't see any role or group information.