Change the properties of a SQL Server login account

Enabling a disabled login
The following example enables the login Emma.

ALTER LOGIN Emma ENABLE

Changing the password of a login
The following example changes the password of login Emma to 123EEDWQxz829.

ALTER LOGIN Emma WITH PASSWORD = '123EEDWQxz829'


Changing the name of a login
The following example changes the name of login Emma to DavidB.

ALTER LOGIN Emma WITH NAME = DavidB

Changing the Name of a Database User

The following piece of T-SQL changes the name of a database user named james to jesse.


Use AdventureWorks;
Alter USER Lara WITH NAME = jesse
GO

Display Identity Columns in all Tables

This piece of T-SQL displays all the identity columns in all the tables in a Database.

Use Adventureworks
Go
Select o.name, i.name As 'ColumnName'
From sys.identity_columns i, sys.objects o
Where i.object_id = o.object_id
Go

Lock Monitor

Lock Monitor thread detects deadlock. It does that by inspecting processes ecery 5 seconds to determine if there are any deadlock cycles. When it finds one, it automatically chooses one thread as the deadlock victim. It then rolls back the
victims transaction, cancel its query and returns error 1205.

The Lock Monitor chooses the deadlock victim by choosing the least expensive transaction to roll back.
You can overide this by using SET DEADLOCK_PRIORITY to low

Determining System and Server Information

The system and server information is available on the General page of the server properties dialog box. You can also use the extended stored procedure xp_msver.

exec xp_msver