Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Getting IS_Member to work as expected Expand / Collapse
Author
Message
Posted Thursday, August 28, 2008 12:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:56 AM
Points: 115, Visits: 317
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




Doug
Post #560731
Posted Thursday, August 28, 2008 5:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 5,584, Visits: 6,376
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #560907
Posted Thursday, August 28, 2008 10:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:56 AM
Points: 115, Visits: 317
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





Doug
Post #560982
Posted Friday, August 29, 2008 3:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 5,584, Visits: 6,376
Is Admin_role a database role or an application role?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #561059
Posted Friday, August 29, 2008 8:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:56 AM
Points: 115, Visits: 317
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.




Doug
Post #561269
Posted Friday, August 29, 2008 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:56 AM
Points: 115, Visits: 317
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'





Doug
Post #561302
Posted Tuesday, September 2, 2008 4:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 5,584, Visits: 6,376
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #562301
Posted Tuesday, September 2, 2008 7:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,281, Visits: 4,225
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
Post #562386
Posted Tuesday, September 2, 2008 10:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 5,584, Visits: 6,376
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #562511
Posted Tuesday, September 2, 2008 10:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:56 AM
Points: 115, Visits: 317
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




Doug
Post #562519
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse