1

Link: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#summary

Is this true that Members of the local computer administrator group can always elevate their privileges to sysadmin.

I am confused because the local administrator group is not automatically added to the sql security post 2008. And so they cannot login into the SQL server instance.

0

3 Answers 3

4

Yes, a user with Local Administrator level access can do literally anything they want to the system.

Take a look at this question-and-answer that explains how to add a user to the sysadmin SQL Server role when no current sysadmin is available.

3
  • Does this only work when "single user mode"?
    – variable
    Aug 20, 2021 at 5:32
  • Yes. The ability to login as a Windows administrator now being sysadmin in SQL Server even if your windows account doesn't exist in SQL Server requires starting SQL Server in single user mode. Aug 20, 2021 at 7:14
  • @Tibor - take a look at the link I edited into the answer above which shows how to do that without starting SQL Server in single user mode. I thought it was quite interesting. Also, I wouldn't recommend taking that approach on a production system, but nonetheless it can be done.
    – Hannah Vernon
    Aug 20, 2021 at 19:42
3

You have to bear in mind that a user with local administrator rights can literally do anything they want, including for example, attaching a debugger to sqlservr.exe process and modifying code or memory directly. They could also take ownership of the master database file and modify it with a hex editor.

You cannot lock them out, because there are many ways for them to elevate to SYSTEM rights, at which point they can do anything they want anyway.

For example, one of the methods on the excellent post linked by @StephenMorris uses impersonation. An administrator can impersonate any user, so they could act as the MSSQLSERVER user.

1

am confused because the local administrator group is not automatically added to the sql security post 2008. And so they cannot login into the SQL server instance.

Adding to the correct technical answers, the Windows Admin owns the server. And so while we don't want Windows Admins connecting to the SQL instance as a privileged user (or at all) by default, the Windows Admin must always be able to "take ownership" of the instance and the data.

By analogy a home may have locks on bedroom or bathroom doors, but these aren't intended to permanently deny access to the homeowner. Only to prevent accidental or casual access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.