Admin: Windows Group Login Ambiguity

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. 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. So whats the problem? Well, consider this example:

User - DOMAIN\PeterPan
Groups - DOMAIN\ReadOnlyGroup, DOMAIN\WriteOnlyGroup

We have 2 windows groups both of which have access to SQL Server, albeit for different purposes. Peter is a member of both groups as he needs access to do both actions. My question is, when Peter logs in, under which Windows Group has he been granted authentication?

Why does it matter, I here you ask?

From a pure permissions point of view, there is no issue as the usual hierarchy applies. 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. 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:

EXEC sp_addrolemember N'db_datareader', N'DOMAIN\ReadOnlyGroup'

EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\WriteOnlyGroup'

You can view the permission set in the following way:

SELECT *, DB_NAME(), SUSER_SNAME() FROM fn_my_permissions(NULL, 'DATABASE')

However, this works because there is a defined hierarchy in place which allows SQL Server to make the correct choice based upon permission settings.

But, what about something where no such hierarchy exists like a default language. 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. US_English and British_English interpret dates in MDY and DMY respectively so the date 30/11/2011 will throw an error in US_English but work correctly in British_English. Worse still, you may not get an error at all and the date is valid but not what the user wants e.g 01/02/2011. Of course, best practice would see us using ISO formatted dates YYYYMMDD but I dare say not every application adheres to this.

I understand that this is a difficult (if not impossible) issue to rectify. I mean, there is no logical hierarchy or set of rules that can be put in place when this situation occurs. However, I'd like to understand how SQL Server does determine which language it uses in this scenario as I couldn't find a method of doing so. Is it the windows group which was last granted access to SQL? Does anyone know?

Powershell: List Local Users & Groups

I recently encountered a permissions issue on SQL Server, the route cause of which was a user being removed from a local windows group on a production server. This got me thinking of yet another use for Powershell, to list out the local users and groups on a particular server.

ListLocalGroupMembership.ps1
$computerName = Read-Host 'Enter computer name or press <Enter> for localhost'  
  
if ($computerName -eq "") {$computerName = "$env:computername"}
$computer = [ADSI]"WinNT://$computerName,computer"
$groups = $computer.psbase.Children | Where-object { $_.psbase.schemaclassname -eq 'group' }

foreach ($group in $groups)
{
   $users = $group.psbase.invoke("Members")
   $group.name
   write-host "----------------------------------------------------"

   if ($users -ne $null)
   {
       foreach ($user in $users)
       {
           $user.GetType().InvokeMember("Name","GetProperty",$null,$user,$null)
       }
   }
   else
   {
       write-host "-----EMPTY-----" -foreground "red"
   }

   write-host "----------------------------------------------------"
   write-host ""
}


The basis of this script was taken from here.

You could easily take this a stage further and log this data to a repository and then alert any changes made.