Home Forums SQL Server 2005 T-SQL (SS2K5) Getting IS_Member to work as expected RE: Getting IS_Member to work as expected

  • August 28, 2008 at 5:20 pm

    #864770

    I got it to work for me when checking a database role of db_Owner.

    It looks like it checks the current user against a Windows Domain account or checks the database roles (not server roles). Which means, whatever user you happen to be logged in as is what it checks.

    If you're logged in as 'Mary' and type "Select IS_MEMBER('db_Owner')" and Mary is a member of your database's db_owner role, it will return 1. If she's not a member, it'll return 0.

    If you're logged in as 'Mary' and type "Select IS_MEMBER([MyDomain\SQLSysAdminGroup])" and Mary is a member of this domain group, it will return 1. If she's not a member, it'll return 0.

    But if you are logged in as 'Tom' and you're trying to check Mary's roles, it won't work at all.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.