How To Add Yourself To SQL

The other day, I locked myself out of my SQL Server by joining the VM to a domain. When I joined the domain, only the local users had access, because I hadn’t added the domain admins to the machine as sysadmin role members yet, since the machine wasn’t yet part of the domain.

It's a circle!

Anyway, I found myself unable to login to my SQL 2012 instance using Windows Authentication. While adding myself back, I discovered that sp_addsrvrolemember has been deprecated.

net stop <<servicename>>
net start MSSQLSERVER /m
sqlcmd –E –S.

Then, in SQL Server 2008 R2 and earlier, I would do this:

create login [DOMAIN\LOGIN] from windows;
go
exec sp_addsrvrolemember [ @loginame= ] 'domain\login' , [ @rolename = ] 'role';
go

But now, in SQL 2012, to be future proof, I have to upgrade my script to look like this:

create login [DOMAIN\LOGIN] from windows;
go
alter server role 'role' add member='domain\login';
go

One MS reference:
sp_addsrvrolemember (Transact-SQL)