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:
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!
