December 12, 2008 at 1:13 pm
In running some cleanup reports(sql2000) I am gathering a list of orphaned users per databases on a server that ONLY own objects. That way, the objects which these accounts can be changed if necessary.
The only thing is, the accounts which are showing are not really orphans....Let me explain.....
Say I have a domain (ABC) and all my users get access through AD groups. Well, if a developer happens to create an object, even though he authenticates through a group, he can assign his domain ID the owner of that object. By doing this, it creates a entry in the sysusers table. So, even tho his group is an entry in sysusers, and he creates a object w/ himself owning it, his id gets an entry as well (the sid matches that in sql and AD as well)....
The other (amazing) issues, is that you can assign ownership of an object to any domain ID! I create a table and assigned my ID ownership of an ID (which I authenticate through a group). In the same, I took a user account in the domain, which has NO access to this SQL box, and it allowed that domain ID to own an object, which, inturn, created him an entry in SysUsers.......
So, now, I have many results in orphaned logins where it's not entirely true.......It's acting more-or-less as schemas than accts...
December 12, 2008 at 1:19 pm
That's an interesting issue.
If someone gets rights through a group, who should own the object? Off the top of my head lean towards the group, which in many ways makes management easier.
However, the user really needs to map to a specific login since there is an auditing issue here as well.
December 12, 2008 at 1:25 pm
Right, but that just really is confusing why it's handled this way in MSSQL. Especially allow anyone w/in AD to own the object regardless if they exist on the SQLServer itself...gooofy.
But, regardless, I would (personally) think the dbo account of the DB would own all the objects. That way, you will never have ownership issues. I wouldn't think you would want any individual owning objects. Would be hard to remove their account when required.
But, again..that is my problem. B/c, I would hate to remove this "orphan" and change ownership of these objects when these accounts might be getting access through one of the groups. And, unfortunately, being at a huge company w/ thousands of sql boxes, it's anything but easy to review all of these....
December 15, 2008 at 12:40 pm
Perhaps that is why in SQL2005 and later it separates the USER from the SCHEMA
so an AD group will own objects via the SCHEMA, and you can modify users/schema without affecting the objects
December 15, 2008 at 1:13 pm
That very well might be. I am yet to test this situaltion in 2005, but I'm 99% sure that was the probably the main reason to break it out.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply