August 28, 2008 at 12:22 pm
I have custom role with permissions called Admin_role. however, when I try to determine if current user is in my admin role using the sytem IS_MEMBER() function the result is always False (=0). I First thought was that I needed to specify the user as MyDomain\myuser, but still it does not work.
SELECT IS_MEMBER('Admin_Role') AS IsMember
-- This always returns False, IsMember = 0
Is there another query that would get me the same answer using any of the system informational schema views, etc.
Thanks
August 28, 2008 at 5:20 pm
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.
August 28, 2008 at 10:25 pm
Thanks Brandie, but it just does not seem to work with my own roles. It works fine with db_owner, but my roles always show FALSE.
Try this with your own roles and tell me if I'm going crazy or not.
When I use Mgmt Studio as: myDomain\user and put the user in the role this way it just does not return TRUE as expected.
If you have time try this with a user defined role (not a sys role).
SELECT IS_MEMBER('Admin_Role') AS IsAdmin,DB_NAME(),SUSER_NAME() AS Suser_name, IS_MEMBER('db_owner') AS IsDBO,DB_NAME(),USER_NAME() AS UserName
This one returns: IsDbo= 1(true) and IsAdmin=false. Even though role membership disagrees. User name is mydomain\user, login is mydomain\user when I look at User properties.
I've also tried this as username=user and login name = mydomain\user.
I've checked your posts Brandie, your a good DBA, thanks in advance. I hope you can provide a sanity check.
Cheers
August 29, 2008 at 3:10 am
Is Admin_role a database role or an application role?
August 29, 2008 at 8:56 am
It's a database role.
I've this does not work I'll write my own proc. Have you ever used sys.database_role_members? or any of the related tables.
Thanks again Brandie.
August 29, 2008 at 9:25 am
Brandie, this basically get me the result, that shows the member in the database role. I'll be darned why the IS_MEMBER() does not work.
SELECT DPR.NAME, DPR.Principal_id, Member.*, DPU.Name
FROM sys.database_principals DPR
INNER JOIN sys.database_role_members Member ON DPR.principal_id = Member.role_principal_id
INNER JOIN sys.database_principals DPU ON member.member_principal_id = dpu.principal_id
WHERE DPR.NAME = 'Admin_role'
AND dpu.NAME = 'username'
September 2, 2008 at 4:33 am
Sorry, Doug. I was out for the weekend so didn't see your replies.
Hmm. BOL says that it includes database fixed roles or user-defined roles. And in testing myself added to one of our user-defined roles, I'm seeing false as the result when it should be true.
What Service Pack are you on? I'm on SP1 at the moment. I've tried testing against SP2, but SQL won't let me add myself as an individual user to a database because I'm part of the SysAdmin domain group.
I think this might be a bug. Have you reported it to Microsoft yet? Log into Microsoft Connect at http://connect.microsoft.com/SQLServer and then it has a features submission & bug submission page. See if it's already been submitted. If not, submit it yourself.
September 2, 2008 at 7:28 am
The system function works as defined, which is for the current database user
When connection with a domain account with sysadmin role:
select is_member('UserRole'), user_name(), suser_sname()
execute as user = 'Carl.Federl'
select is_member('UserRole'), user_name(), suser_sname()
revert
go
Output is:
0 dbo AAUSA\carl.federl
1 Carl.Federl Carl.Federl
SQL = Scarcely Qualifies as a Language
September 2, 2008 at 10:39 am
Carl, I appreciate your response, but your explanation doesn't give many details.
What type of user role (ie, what permissions or roles were assigned) did you use for your experiment? I tried a user role using other database roles. Did you do the same or did you have distinct permissions on your user role. Also, was your domain group part of the role, or just your individual login?
Also, were you testing on SP2 or SP1?
September 2, 2008 at 10:59 am
Thank you both for posting. Brandie, you confirmed what I was seeing. And Karl you showed me what I suspected all along, that there's some problem with aliasing my user name.
It's a shame that BOL does not explain this better and that Is_member requires the run as statement to make it work as expected.
Thanks for your help, I hope to return the favor sometime.
Doug
November 29, 2013 at 10:08 am
SELECT COALESCE((SELECT 1
FROM sys.database_principals DPR
INNER JOIN sys.database_role_members Member ON DPR.principal_id = Member.role_principal_id
INNER JOIN sys.database_principals DPU ON member.member_principal_id = dpu.principal_id
WHERE DPR.NAME = 'MyRole'
AND dpu.NAME = ORIGINAL_LOGIN() ),0) as MyRole
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply