If the 2 groups have different default languages then how do we determine which default language the login connection should take? This is particularly important when dealing with dates. However, this works because there is a defined hierarchy in place which allows SQL Server to make the correct choice based upon permission settings.īut, what about something where no such hierarchy exists like a default language. SELECT *, DB_NAME (), SUSER_SNAME () FROM fn_my_permissions (NULL, 'DATABASE' ) You can view the permission set in the following way: You can validate this by simply adding a differing set of permissions to the different groups and then adding or removing the member from it:ĮXEC sp_addrolemember N'db_datareader', N'DOMAIN\ReadOnlyGroup'ĮXEC sp_addrolemember N'db_datawriter', N'DOMAIN\WriteOnlyGroup' Permissions are built on top of each other so SQL Server will combine the permissions from both groups (DENY overriding GRANT etc) and that gives us the behaviour we want.
My question is, when Peter logs in, under which Windows Group has he been granted authentication?įrom a pure permissions point of view, there is no issue as the usual hierarchy applies. Peter is a member of both groups as he needs access to do both actions. We have 2 windows groups both of which have access to SQL Server, albeit for different purposes. Groups - DOMAIN\ReadOnlyGroup, DOMAIN\WriteOnlyGroup So whats the problem? Well, consider this example: However, its not uncommon for a user to belong to multiple Windows groups and its possible that these groups could each have a SQL login. Its a typical scenario to grant login access to SQL Server via Windows groups as this eases the administrative burden of setting up multiple logins all with the same access.