• Security has changed in Windows 2008 and above. The problem you are seeing is not caused by SQL Server, but be Windows 2008.

    You used to be able to add a domain group to a local group, and have the domain group members inherit the rights of the local group. This has changed with W2008. If you look at the W2008 documentation you will see that many of the local groups are now shown as deprecated, and already the security works in a different way for these groups.

    From what I can see, any login that has special rights by way of their local group membership now has two security contexts: a normal User context and a privileged context. Everything that the login does is run under the normal user context unless they specifically request the privileged context (i.e. Run as Administrator).

    One impact of this is that if you add Domain Admins to the local admin group, when they connect to SQL Server their security context is no longer 'Local Administrators'.

    One way I have found to overcome this issue is to add the relevant groups directly into SQL Server, and not rely on inherited permissions from the local server groups.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara