SQL Server

Reset sa password

You may lose admin access to the server if all you had was access via a Windows user, then cloned to a new VM.

You can use "DAC" command console to reset the sa password if you don't know it:

• restart sql service (from Services screen) with -m -f parameters
• run CMD AS ADMIN, follow this:

(this looks successful but did nothing (no user added))
C:\Windows\system32>sqlcmd
1> create login MACHINENAME\USERNAME from windows
2> sp_addsrvrolemember 'MACHINENAME\USERNAME', 'sysadmin'
3> go
1> exit

(this worked)
C:\Windows\system32>sqlcmd
1> alter login sa enable
2> go
1> sp_password NULL, 'sa', 'sa'
2> go
Msg 15116, Level 16, State 1, Server MACHINENAME, Line 1
Password validation failed. The password does not meet the operating system policy requirements because it is too short.
1> sp_password NULL, 'Fancy1234!@#$', 'sa'
2> go
1> quit

Case sensitivity

SQL Server is case-insensitive by default, to check specifically:

select serverproperty('collation')
SQL_Latin1_General_CP1_CI_AS        < the CI indicates case-insensitive