IS_MEMBER

[sharonc]

This is my view that I'm using to secure the application.

SELECT dbo.[JobCashFlow Table].*
FROM dbo.[JobCashFlow Table]
WHERE (Project_Owner = SYSTEM_USER) OR
(IS_MEMBER('Manager') = 1)

Can someone tell me why the IS_MEMBER is not catching my role 'Manager'? iF i PLACE myself in the Manager group, it still only shows me the 14 records that have project_owner equal to my login name. I'm using Windows NT Authentication.

 

[tlbroadbent]

IS_MEMBER requires the domain name as well as the user name.

(IS_MEMBER('domain\Manager') = 1)
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[sharonc]

I changed my view to:

SELECT dbo.[JobCashFlow Table].*
FROM dbo.[JobCashFlow Table]
WHERE (Project_Owner = SYSTEM_USER) OR
(IS_MEMBER('aepco-ssw\Manager') = 1)


iF i PLACE myself in the Manager group, it still only shows me the 14 records that have project_owner equal to my login name.

iF i DO A exec sp_Helpuser, it shows the username as sharonc, the groupname as manager, and the loginname as aep-ssw\sharonc

Is there something else I'm not doing correctly?

 

[tlbroadbent]

1- If Manager is a database Role rather than NT Group, you don't need the Domain name. Sorry, I read the NT authentication and made an incorrect assumption.

2- If your login is a system admin, it will not show as a member of any other database role except db_owner because a system adminstrator is the db_owner in every database. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[sharonc]

My login is a system admin and db_owner, but I still don't show as being in the manager group when I execute

select is_member('Manager')

If I run
SELECT SYSTEM_USER then I get this: AEPCO-SSW\SHARONC

If I run select is_member('public') , I do show I'm in the public group.

Manager is a SQL Server role

Any other suggestions?

 

[tlbroadbent]

I don't believe you will show as a member the database role 'Manager' because you are a system admin. I don't know of an answer except to test for db_owner, also.

WHERE (Project_Owner = SYSTEM_USER)
OR (IS_MEMBER('Manager') = 1)
OR (IS_MEMBER('db_owner') = 1)

Other users shouldn't encounter this issue because they will not be SA's. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[sharonc]

your right, I show up as being in the db_owner group. I think I'll test it on someone elses machine and see what happens.

Thank you for all of your help. I really appreciate it.

 

[sharonc]

tHANK YOU VERY MUCH. i TESTED IT ON SOMEONE ELSES MACHINE AND THE SECURITY IS WORKING JUST FINE.