Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S 
Author Message
 Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

I have a couple of users in my database which belong to
specific roles. Unfortunately, is_member('roleXY') returns
0 for some users although they belong to this role.

I know that there is a fix for the SQL Server 7.0
regarding to a similar problem (FIX: Execution of
IS_MEMBER May Cause AV When Many Roles Exist [Q246189])

**FROM MSDN****************
BUG #: 56457 (SQLBUG_70) BUG #: 56457 (SQLBUG_70)
Symptoms
When many roles exist in a database, and you use the
IS_MEMBER() T-SQL function to determine if the current
user is a member of a particular role, you may receive an
error similar to the following:
ODBC: Msg 0, Level 19, State1 SqlDumpExceptionHandler:
Process 8 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.
Use IS_SRVROLEMEMBER('role') instead. Refer to SQL Server
Books Online for specifics on the use and syntax of this
function.
******************

Is this problem also known in SQL Server 2000 since I
sometimes get similar error messages.

Any help is greatly appreciated. Thanks in advance.

   Daniel Walzenbach



Sat, 09 Apr 2005 15:47:13 GMT
 Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

I have been researching the same thing.  What I found is that if your
user is also part of a server role such as System Administrator,
IS_MEMBER does not return the correct value.  If you use
sp_helprolemember 'roleXY' the MemberName column returns the correct
list.  Go figure...
Quote:

> I have a couple of users in my database which belong to
> specific roles. Unfortunately, is_member('roleXY') returns
> 0 for some users although they belong to this role.

> I know that there is a fix for the SQL Server 7.0
> regarding to a similar problem (FIX: Execution of
> IS_MEMBER May Cause AV When Many Roles Exist [Q246189])

> **FROM MSDN****************
> BUG #: 56457 (SQLBUG_70) BUG #: 56457 (SQLBUG_70)
> Symptoms
> When many roles exist in a database, and you use the
> IS_MEMBER() T-SQL function to determine if the current
> user is a member of a particular role, you may receive an
> error similar to the following:
> ODBC: Msg 0, Level 19, State1 SqlDumpExceptionHandler:
> Process 8 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
> process.
> Use IS_SRVROLEMEMBER('role') instead. Refer to SQL Server
> Books Online for specifics on the use and syntax of this
> function.
> ******************

> Is this problem also known in SQL Server 2000 since I
> sometimes get similar error messages.

> Any help is greatly appreciated. Thanks in advance.

>    Daniel Walzenbach



Wed, 13 Apr 2005 00:38:32 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

2. xp_sendmail on SQL Server 8.00.534 fails with error 0x80040111

3. Anyone know what version of SQL Server 7 has this version#: 7.00.718

4. Upgrading SQL 2000 SP2 8.0.534 to 8.0.679

5. Strange behaviour in SQL Server 2000?

6. [Fwd: Product News - Version 3.00.1e]

7. SQLSVR.dll version 2000.080.0194.00

8. Parameterized Queries in DTS using SYBASE 11 Driver Version 3.01.00.00

9. D4.DB Date 00=1900, 00<>2000

10. D4.DB Date 00=1900, 00<>2000

11. Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679

12. MS SQL Server 2000.80.194.00


 
Powered by phpBB® Forum Software