Have Geek Brain

When is IS_MEMBER installed on Sql Server?

I am trying to get IS_MEMBER to work in UDFs on Sql Server 2005.

I have a "Domain \ TestGroup" window group.

I host my Login "Domain \ Kieran".

select SUSER_NAME (); gives "Domain \ Kieran"

but select IS_MEMBER ('Domain \ TestGroup'); returns NULL.

0


a source to share


3 answers


Oh. I think I see the problem.

My login is part of built-in \ Administrators, which is a server member with the sysadmin fixed role.



So I am a member of sysadmin, which means I am dbo and cannot see any role or group information.

0


a source


NULL response from IS_MEMBER means SQL Server does not recognize 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 the Windows group membership by examining the access that Windows creates. The access token does not reflect changes in group membership that are made after the user connects to the instance of SQL Server.

So, while doamin \ kieran is in a group, you will probably have to log out and back in for your login token to update with the group membership.

Everything should work in your UDF (unless you have EXECUTE AS USER or OWNER in UDF)

+1


a source


Check ERRORLOG

and see if there is any error indicating that the SQL Server service cannot connect to the domain's Active Directory.

0


a source






More articles:


All Articles