Group authentication oddity

Question

Group authentication oddity

wmt

SSChasing Mays

Points: 602
More actions
March 15, 2016 at 7:06 am

#309116

Very confused with some security behavior I'm seeing.
I've restored a database from SQL 2008 to SQL 2012.
Within the Database Users, there was a previous Windows group which had db owner. It doesn't exist on the SQL 2012 server.
I've created a new group which only has Read access. Added this to the server and database.
So,
Within the database, I've got:
One Windows group with db owner access which doesn't have a server login.
One Windows group with Read access which does have a server login.
It's the same users in both groups. There aren't any embedded groups i.e. groups within groups or anything like that.
The effective permissions the users have is db owner.
If I remove the old group, they revert back to Read access only.
I would've thought that SQL would've ignored the old Windows group completely, given that it doesn't have a server login.
It's not a Contained database - even partial.
Compatibility level is SQL 2008.
Is this expected behavior?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

yes, it's expected behavior when we are talking about Windows groups.

you just have to take into consideration the situations you are seeing here: multiple groups, and the cumulative inheritance of permissions that can provide.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wmt SSChasing Mays Points: 602 More actions · Answer 2

thanks for that.

I'd always presumed that a windows group named at a database level needed a corresponding server login, regardless of what other access the user had to the instance via other groups.