SQL Server: Difference between revisions

From Bitpost wiki
(Created page with "=== Case sensitivity === SQL Server is case-insensitive by default, to check specifically: select serverproperty('collation') SQL_Latin1_General_CP1_CI_AS < the CI in...")
 
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
=== 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 ===
=== Case sensitivity ===
SQL Server is case-insensitive by default, to check specifically:
SQL Server is case-insensitive by default, to check specifically:
  select serverproperty('collation')
  select serverproperty('collation')
  SQL_Latin1_General_CP1_CI_AS        < the CI indicates case-insensitive
  SQL_Latin1_General_CP1_CI_AS        < the CI indicates case-insensitive

Latest revision as of 19:49, 20 December 2018

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