Professional Web Applications Themes

Problem with IS_MEMBER and integrated NT security - Microsoft SQL / MS SQL Server

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?...

  1. #1

    Default Problem with IS_MEMBER and integrated NT security


    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?

    Adrian Guest

  2. #2

    Default Re: Problem with IS_MEMBER and integrated NT security

    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

    "Adrian" <adrian.yatesno-spam-ngis-com-au> wrote in message
    news:com... 



    Tim Guest

  3. #3

    Default Re: Problem with IS_MEMBER and integrated NT security



    On Mon, 14 Jul 2003 20:56:47 -0500, "Tim S" <com> wrote:
     

    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.
    Adrian Guest

  4. #4

    Default Re: Problem with IS_MEMBER and integrated NT security

    On Tue, 15 Jul 2003 11:56:48 +0800, Adrian
    <adrian.yatesno-spam-ngis-com-au> wrote:
     
    >
    >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.[/ref]

    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).
    Adrian Guest

Similar Threads

  1. CF 5 with IIS Windows Integrated Security
    By treedevil in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 16th, 02:19 AM
  2. Integrated security in ASP.net
    By Nick in forum ASP.NET Security
    Replies: 6
    Last Post: August 19th, 02:39 AM
  3. Integrated Security
    By Srinivasa Reddy K Ganji in forum ASP.NET Security
    Replies: 12
    Last Post: September 12th, 02:46 PM
  4. ASP.NET & Integrated Security setup?
    By Dave in forum ASP.NET Security
    Replies: 0
    Last Post: August 8th, 05:29 PM
  5. How to access to SQL with Integrated Security from ASP.Net app
    By Davide Bedin in forum ASP.NET Security
    Replies: 0
    Last Post: July 24th, 03:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139