MSSQL

Basic Commands

Azure SQL Database

Create LOGIN/USER

-- Create LOGIN
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
-- OR
USE [master]
GO
CREATE LOGIN [readonlylogin] WITH PASSWORD=N'1231!#ASDF!a', DEFAULT_DATABASE=[TESTDATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Create USER
USE TESTDATABASE
GO
CREATE USER readonlyuser FROM LOGIN readonlylogin;
GO

-- CREATE USER WITHOUT LOGIN
CREATE USER readonlyuser WITH PASSWORD = '1231!#ASDF!a';

-- CREATE USER ON THE DATABASE
CREATE USER username WITH PASSWORD = 'MySuperS3cretP@ssw0rd';
ALTER ROLE [db_datareader] ADD MEMBER [username];
ALTER ROLE [db_datawriter] ADD MEMBER [username];
ALTER ROLE [db_owner] ADD MEMBER [username];

Drop User

DROP USER username;

Set Permission

-- ADD ROLE MEMBER
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

-- OR
USE TESTDATABASE
GO
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser];
GO

Get Permissions

-- GET ALL PERMISSIONS EXPLICITLY GRANTED OR DENIED
SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

Get Detailed Permissions

-- GET DETAILED PERMISSIONS
;WITH 
[explicit] AS (
   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date],
         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],
         CAST('' AS SYSNAME) [grant_through]
   FROM [sys].[database_permissions] [dbp]
   INNER JOIN [sys].[database_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]
   WHERE ([dbp].[type] IN ('IN','UP','DL','CL','DABO','IM','SL','TO') OR [dbp].[type] LIKE 'AL%' OR [dbp].[type] LIKE 'CR%')
     AND [dbp].[state] IN ('G','W')
   UNION ALL
   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]
   FROM [sys].[database_principals] [dp]
   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]
   ),
[fixed] AS (
   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[name] [permission], CAST('' AS SYSNAME) [grant_through]
   FROM [sys].[database_principals] [dp]
   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
   INNER JOIN [sys].[database_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]
   WHERE [p].[name] IN ('db_owner','db_datareader','db_datawriter','db_ddladmin','db_securityadmin','db_accessadmin','dbmanager','loginmanager')
   UNION ALL
   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]
   FROM [sys].[database_principals] [dp]
   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]
   )
SELECT DISTINCT DB_NAME() [database], [name] [username], [type_desc], [create_date], [permission], [grant_through]
  FROM [explicit]
 WHERE [type_desc] NOT IN ('DATABASE_ROLE')
UNION ALL
SELECT DISTINCT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]
  FROM [fixed]
 WHERE [type_desc] NOT IN ('DATABASE_ROLE')
ORDER BY 1, 2
OPTION(MAXRECURSION 10);

Delete User

-- DELETE/REMOVE USER
DROP USER readonlyuser;

Get All databases

-- LIST ALL DATABASES
select [name] as database_name,
    database_id,
    create_date
from sys.databases
order by name

Get all tables

-- LIST ALL TABLES
select schema_name(t.schema_id) as schema_name,
       t.name as table_name,
       t.create_date,
       t.modify_date
from sys.tables t
order by schema_name,
         table_name;

Describe Table columns

exec sp_columns Employee;

Delete All data on a table

truncate table Employee;

Get Sessions

exec sp_who2;

Docs

• Database-Level Roles
• Adding Users to Your SQL Azure Database
• sys.database_permissions (Transact-SQL)
• Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics