Can’t log into SQL Server 2012 with domain admin account

I recently migrated a database from SQL Server 2008 to a new server running SQL Server 2012. Everything worked fine, but it was annoying me that I couldn’t log into the database (using SQL Server Management Studio) as a domain administrator – specifically DOMAIN\administrator. Logging in as the SQL user “sa” worked fine, but using Windows Authentication I got the error:

Now, I’ve had similar to this before, and the usual cause is because “Server Authentication” isn’t set to “SQL Server and Windows Authentication Mode” – but normally it’s the “sa” account that can’t log in, and the domain admin can.

Logging in as “sa”, I checked this section of the server properties:

sqlserversecurity

Which shows what I’d expect. It seems that the user just wasn’t a valid user in the SQL Server 2012 security table. To fix that, you need to run:

and to make them an SQL admin, run this:

Finally, restart the SQL server service (actually, I’m not sure if this was necessary, but I did it anyway) and you can log in as the user you just added. Hopefully!

Leave a Reply