Problem with IS_MEMBER and integrated NT security

Problem with IS_MEMBER and integrated NT security

Post by Adria » Wed, 16 Jul 2003 03:06:01



I've found a strange issue with using IS_MEMBER on integrated NT
logons.

I have two logons:
 DOM\User1: A windows NT logon with access to the database
 User2: A SQL Server logon

I have a single role, called Sales.  Both users are set-up with
identical permissions and are members of the Sales role.

But when I call IS_MEMBER('Sales') while logged on as the first user,
it always returns 0.  User2 works fine.

Any ideas?

 
 
 

Problem with IS_MEMBER and integrated NT security

Post by Tim » Wed, 16 Jul 2003 03:56:47


I suggest verifing the results of
SELECT USER_NAME()
SELECT SYSTEM_USER
are the same for "DOM\User1" if its is dbo for USER_NAME() I think it is an
already known bug. My memory could be wrong.

Tim S


Quote:

> I've found a strange issue with using IS_MEMBER on integrated NT
> logons.

> I have two logons:
>  DOM\User1: A windows NT logon with access to the database
>  User2: A SQL Server logon

> I have a single role, called Sales.  Both users are set-up with
> identical permissions and are members of the Sales role.

> But when I call IS_MEMBER('Sales') while logged on as the first user,
> it always returns 0.  User2 works fine.

> Any ideas?


 
 
 

Problem with IS_MEMBER and integrated NT security

Post by Adria » Wed, 16 Jul 2003 05:56:48



>I suggest verifing the results of
>SELECT USER_NAME()
>SELECT SYSTEM_USER
>are the same for "DOM\User1" if its is dbo for USER_NAME() I think it is an
>already known bug. My memory could be wrong.

>Tim S

Your suspicions are correct - DOM\User1 is logged on as dbo
(USER_NAME() returns 'dbo', but SYSTEM_USER returns 'DOM\User1')

Following on this track I tried IS_MEMBER('db_owner'), which returned
1 (True), even though DOM\User1 is not the db_owner!  (sa is the db
owner, and is aliased to dbo).

I will try this with an NT account without any permission on the
database (except for public) and see how that goes.

 
 
 

Problem with IS_MEMBER and integrated NT security

Post by Adria » Wed, 16 Jul 2003 06:19:32


On Tue, 15 Jul 2003 11:56:48 +0800, Adrian



>>I suggest verifing the results of
>>SELECT USER_NAME()
>>SELECT SYSTEM_USER
>>are the same for "DOM\User1" if its is dbo for USER_NAME() I think it is an
>>already known bug. My memory could be wrong.

>>Tim S

>Your suspicions are correct - DOM\User1 is logged on as dbo
>(USER_NAME() returns 'dbo', but SYSTEM_USER returns 'DOM\User1')

>Following on this track I tried IS_MEMBER('db_owner'), which returned
>1 (True), even though DOM\User1 is not the db_owner!  (sa is the db
>owner, and is aliased to dbo).

>I will try this with an NT account without any permission on the
>database (except for public) and see how that goes.

I have tested with another NT account and it works fine.  It seems
that the account DOM\User1 has some special permissions on this
server, and is automatically logged on as dbo.  Unfortunately
IS_MEMBER doesn't work correctly with dbo users, and always return 0
(false).