I have created a SQL 2016 (Developer) instance on a new build machine. For ease of use, when we do this, we grant
Builtin\Administrators sysadmin rights on the instance. This allows all developers on the machine to be able to have full control over their local instance.
In previous versions of SQL (e.g. 2014). This works fine. However, when I try it on SQL 2016, this does not appear to work.
Whenever I attempt to log into the machine with Windows Authentication, I receive the following error:
Login failed for user 'xxxx'. (.Net SqlClient Data Provider)
Server Name: xxxx
Error Number: 18456
Line Number: 65536
Below are shots of the
Builtin\Administrators group within SQL:
As you can see, it's set up and enabled.
The server is configured for Mixed Mode authentication. If I add a Login for the user directly, Windows Authentication will work successfully.
Below is the error log from SQL, which includes a bit more information:
For clarity, the user is in a group which is a member of the Administrators group on the machine. Running
whoami /groups on the commandline confirms that the local Administrators group is listed.
What do I need to do to get
Builtin\Administrators to allow successful logins?