A cheat sheet for T-SQL database administration

tsql-cheatsheet.md

T-SQL Database Administration Cheat Sheet

Credits

I collected these commands mainly from Microsoft Documentation. Credits goes to Microsofts Docs unless credits are stated under the code snippet.

Commands

Select Login

SELECT * FROM sys.sql_logins WHERE name = 'myapp';

Create login minimalist

CREATE LOGIN myapp  WITH PASSWORD = 'password'; 

Create login with options

CREATE LOGIN myapp  
WITH PASSWORD = 'password' MUST_CHANGE,  
CREDENTIAL = RestrictedFaculty,  
DEFAULT_DATABASE = MyDatabase,
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF ;

Alter login

ALTER LOGIN myapp WITH PASSWORD = '<enterStrongPasswordHere>';

More alter login commands available in MS Docs ALTER LOGIN (Transact-SQL)

Drop login

DROP LOGIN myapp;  

Create a Database User

CREATE USER userName FOR LOGIN myapp

Note: FOR LOGIN and FROM LOGIN both works.

Select Database Users

Use [Database_Name]
GO
SELECT name, type, type_desc, default_schema_name, authentication_type_desc 
FROM sys.database_principals
WHERE type in ('S','U')
GO

Drop Database User

DROP USER IF EXISTS userName  

Add a Database User (using sp_addrolemember)

Use [Database_Name]
GO
EXEC sp_addrolemember 'db_owner', 'userName'  
GO

Add a Database User (using Alter Role)

Use [Database_Name]
GO
ALTER ROLE db_datareader ADD MEMBER userName
GO
--Notice that the apostrophe (') in the Alter Role command is not needed.
--Write the role name and user name without the apostrophes.

Note: Alter Role is recommended because sp_Addrolemember will be removed according to the official documentation.

Change the Identity Value (Reseed)

DBCC CHECKIDENT{'[Database].[dbo].[Table]', RESEED, 0}

Force Rename Database Name

ALTER DATABASE [old_name]
 
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [old_name]
 
	MODIFY NAME = [new_name]
GO
ALTER DATABASE [new_name]
	SET MULTI_USER
GO

credits: https://michaeljswart.com/2010/04/forcefully-rename-a-sql-server-database/

Change database file path

USE master
GO

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB, FILENAME = 'C:\MSSQL\UserDBData\TestDB.mdf')

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB_log, FILENAME = 'C:\MSSQL\UserDBLog\TestDB_log.ldf')

credits: https://www.mssqltips.com/sqlservertip/6689/sql-server-move-database-files/