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

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:

Error Number: 18456
 Severity: 14
 State: 1
 Line Number: 65536

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:

create login [DOMAIN\administrator] from windows;

and to make them an SQL admin, run this:

exec sp_addsrvrolemember 'DOMAIN\administrator', 'sysadmin';

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.