February 12, 2015 at 8:32 am
I'm migrating a DB from Win Server 2003 / SQL Server 2000 to Win Server 2008 R2 / SQL Server 2008. It's used by a 3rd party application (time clock entry).
Domain\User1 is a member of the domain group TCUsers, which in turn has as its sole member the domain group called Domain Users. The SQL Server login is Domain\TCUsers, mapped to a DB user of the same name, with some but not all permissions (roles) on the DB. Everything works fine in this scenario.
When I create a new (domain) group called Domain\TCAdmins, and put Domain\User1 (and some others) in it, and create a SQL Server login and DB user of the same name, and give it every permission (role) in the book (in that particular DB), then things quit working -- even things that don't need the extra permissions. I know what you're thinking: the Domain\TCAdmins setup contains a Deny somewhere. Well, no, it doesn't.
(For simplicity I'll now stop putting Domain\ in front of everything -- just remember these are domain users and domain groups.)
When I remove User1 from TCAdmins, the program starts working again. Put it back in TCAdmins and it stops working. I even deleted the TCAdmins DB user and login, and it started working. Re-added them from scratch, and it quit working. I cross-checked all the DB settings on the TCUsers group vs. the TCAdmins group, and everything is the same except for the additional DB roles checked in TCAdmins.
The SQL Server log shows the user Domain\User1 successfully logging in to SQL Server, with "Connection made using Windows authentication." But even though logged in to the server instance, the simple clock-in program no longer can access the DB tables it needs, even read-only. It can't even pass the initial "test connection" function.
Everything I read on the web says the group permissions should be cumulative to the user who is a member of multiple groups, and adding another group doesn't lose you any permissions unless the other group has a Deny somewhere. But for some reason that's not happening here. So what am I missing?
P.S. I should clarify that the present system uses SQL Server authentication, not Windows authentication, so that's why it works now. If I try SQL Server auth in the new DB, it works there too. But I was hoping to use this upgrade as an opportunity to go to Windows auth.
February 16, 2015 at 2:54 pm
All right, it's egg-on-face time. When I said I gave the new group "every permission in the book," I didn't notice that two of those "permissions" were db_denydatareader and db_denydatawriter. I truly didn't see them, right in the middle of the list of "allow" roles, alphabetized between db_ddladmin and db_owner. I never "consciously" set them so I was certain they weren't set.
In my defense, I've never used the "deny" roles before, and wasn't even sure I knew where they were listed. And I was actually copying ALL of those roles from a user in the old database -- yes, the OLD one had the "deny" roles ALSO. I didn't realize it at the time, but it appears it was a username that actually wasn't used by anybody! (I didn't set up that database.)
(Note to self: Get more sleep tonight.....)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply