[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1540
  • Last Modified:

sysusers - hasdbaccess, GID and 'via group membership' status reported in EM?

The summary of this issue is that I have two databases (database A and database B) and one group login (domain\group_x) granted access to both databases.   The way group_x is setup in both databases should be the same but, clearly, it is not.  Database B's setup looks a bit strange or, at least, I don't understand what's going on.  User apps still appear to gain access to both databases via group_x.   The problem for me is that I don't understand what 'via group membership' signifies in EM for group_x in Database B and I also don't understand why GID and hasdbaccess are set to zero in Database B.   It also seems odd that if hasdbaccess = 0 fpr group_x , why is that 'via group membership' is reported in EM for Group_X.   See more detail below followed by an ordered list of questions...

Here's the setup...
**********************
Server login: domain\group_X

Note that this is a 'Windows Only' authentication server.
**********************

Database A:
db user: group_X

Database A's sysusers info appears as I would have expected it.  In EM, under this db's User section, I can see group_X has the expected login name and a 'database access' status of 'Permit'.  Everything is fine.  Closer examination of the sysusers table shows: -

GID = 123
hasdbaccess = 1

********************
Database B:
db user: group_X

Database B's sysusers info looks strange.  In EM, the login name is fine but the 'database access' status for Group_X is 'Via group membership.   Closer examination of the sysusers table shows:-

GID = 0
hasdbaccess = 0

**********************
Some findings:-

Whether or not GID = 0 for Group X does not seem to matter.
If hasdbaccess = 0, users still have access.
If hasdbaccess = 0, 'Via group membership' is displayed in EM for that db user/group.

***********************

Questions:

1. If database B's sysusers entry for Group_X states 'hasdbaccess = 0', how is it that members of that group can still gain access via that group to Database B?

2. When it would seem reasonable to assume that someone granted access for Group_X to this database, why is hasdbaccess = 0 for Group_X in Database B

3. When hasdbaccess = 0, this seems to correspond to 'via group membership' being displayed for a db user/group in EM.  What does 'via group membership' signify when other groups just state 'Permit'?  (please don't say, 'because access is via a group!  Other groups have access to the db but with status = Permit in EM and, in sysusers, hasdbaccess = 1)

4. Why is GID = 0 for Group_X in database B when it is clearly a group that has been granted access to that database?

5. What's the general explanation of these GID/Hasdbaccess/via group membership issues?  


AustinSeven






0
AustinSeven
Asked:
AustinSeven
  • 4
  • 2
1 Solution
 
Eugene ZCommented:
What sql server version\edition\SP do you have?
If it is Sql server 2k with sp3a:

see
INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 Service Pack 3
http://support.microsoft.com/Default.aspx?kbid=810474

SQL Server 2000 Security - Part 6 - Ownership and Object Permissions
http://www.databasejournal.com/features/mssql/article.php/3370701
0
 
AustinSevenAuthor Commented:
Not exactly answering my own question here as I'm even more confused now but... here is some info from Microsoft about the hasdbaccess column in sysusers: -

"The hasdbaccess column is used in a similar way to the hasaccess column in the sysxlogins table. In this case, entries with this flag set to zero are created when a user: has not been granted rights to access the database explicitly but creates objects; is explicitly granted permissions; or is added explicitly to a role. Objects created by a user are always owned by the user, and are not owned by the group through which the user was granted access to the database.  An exception is when a user, who is a member of a role or Windows group, explicitly qualifies the role or group as the object’s owner when the object is created.  In this situation, an entry for the user must exist in the sysusers table so that the object can have the appropriate owner. The entry is created automatically, but the user does not get explicit access to the database automatically because the hasaccess flag is set to zero."

Errrr?  I'm not even sure MS have got this right because I can see some examples where a db user/group in a database has 'hasdbaccess' set to 1 when that db user/group has explicity permissions granted and have membership of db roles.  Also, what on Earth does the following mean?  

"The entry [hasdbaccess] is created automatically, but the user does not get explicit access to the database automatically because the hasaccess flag is set to zero."  

'does not get explicity access to the database automatically'????  All I know is that I have users coming in via groups where that group has 'hasdbaccess = 0' and I can't see that this access is anything other than automatic.  Puzzled by this.

AustinSeven


0
 
AustinSevenAuthor Commented:
EugeneZ,

Yes, SQL Server 2000 Enterprise, SP3a.

I'll check out the links.

Thanks,

AustinSeven
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
AustinSevenAuthor Commented:
EugeneZ,

The sql server in question does not have cross-database ownership chaining enabled so I suppose that neither of those articles apply?  Unless I'm mistaken?

AustinSeven
0
 
Eugene ZCommented:
correct... no cross-ownership..
--------
0.
Now read DB default 'public' role:
 The public role is a special database role to which every database
user belongs. The public role contain default access permissions
for any user who can access the database. This database role cannot
be dropped.
--------------------------------

--------------------------------
--------------------------------
1. Can you specify what kind access have  users? select execute SP, etc.?
2. What not NT db users do you have in your DBs?
3. do you have guest db user any where?
4.run
select * from sysusers where isaliased=1 -- to see db users with alias
-------------------------------------
BTW:
>group login (domain\group_x) granted access to both databases.
what DB A and DB B permissions did you grant to group login (domain\group_x) ?
What server role did you give to group login (domain\group_x) ?
Did you restore this DB with this group as user? or Just added fresh user - domain\group_x?

0
 
AustinSevenAuthor Commented:
EugeneZ,

Thanks for the followup.  The public role in the affected db only had default perms so I don't think that was a factor.   There is a Guest db user in the Master and temp db's but not anywhere else.  I believe that Guest can not be removed from these databases.

select * from sysusers where isaliased=1 -- didn't show any results

The way it was setup was that there was a server login group (windows account group) and in the two databases there was a corresponding db user/group (Group_x) and this group had permissions directly granted - mostly execute on stored procedures.

I've searched a lot on the Internet for info on this issue 'via group membership', GID's and 'hasdbaccess' and I haven't found an answer.  I'm used to setting up and administering sql server security but this is something that I don't understand.   Anyway, yesterday evening, I took the decision with the developer of the database/app that I would try:-

1. scripting out all the object perms granted to the db user group(s)
2. dropping the db user group(s)
3. granting db access to the re-create the same group(s)
4. put back the object permissions the way they were.

The above fixed the issue.   Just to make it clear, app users could do what they needed to before the above change and so the db user groups appeared to be working fine.  The only thing was it looked strange to see 'via group membership' and also to see GID = 0 and hasdbaccess = 0.   After re-creating the db user groups in the offending database, it immediately looked the way it should - no more 'via group membership', GID = 1234 (or whatever) and hasdbaccess = 1.  

I'm no nearer understaning this issue but I've made it go away.  I would have been nice to have figured it out though (I hate mysteries).  

If you have any final thoughts, please let me know.


AustinSeven
0
 
RomModCommented:
The question has been PAQ'd and the 500 points have been refunded.
RomMod
Community Support Moderator
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now