Mircosoft SQL Server cheat sheet

Since I have not worked much with SQL Server, its always a challenge for me to remember how to roles, permissions and do a quick backup, etc.
I don't run into it everyday so I created this cheat sheet to help me remember how things are done the SQL Server way.

Logins / users / roles SQL Server permissions:

-- List principals defined on this database:
use jira881;
go
select * from sys.database_principals

-- List all the grants given to certain principal
use jira850;
go
SELECT pr.principal_id, pr.name as pinciple_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;

-- List all users who own a database on the server
select suser_sname(owner_sid) as 'Owner', state_desc, *
from master.sys.databases

-- List all users defined server wide
select * from master.sys.server_principals

-- List all Server wide logins available and the login type:
select sp.name as login,
   sp.type_desc as login_type,
   sl.password_hash,
   sp.create_date,
   sp.modify_date,
   case when sp.is_disabled = 1 then 'Disabled'
     else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
     on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

-- list users in db_owner role for a certain database:
USE MyOptionsTest;
GO
SELECT members.name as 'members_name', roles.name as 'roles_name',roles.type_desc as 'roles_desc',members.type_desc as 'members_desc'
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
ON rolemem.member_principal_id = members.principal_id
where roles.name = 'db_owner'
ORDER BY members.name

-- List SQL server dbrole vs database user mapping
USE MyOptionsTest;
GO
SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

-- Create a server wide login
CREATE login koki
WITH password = 'Koki_123_123'
go

-- create a user mapped to a server wide login:
USE MyOptionsTest;
GO
CREATE USER koki_mot FOR LOGIN koki
go
USE MyOptionsTest;
GO
EXEC sp_addrolemember 'db_owner', 'koki_mot';
go

USE jira881;
GO
CREATE USER koki_881 FOR LOGIN koki;
go
EXEC sp_addrolemember 'db_owner', 'koki_881';
go

Collations:

use master;
go
create database testdb1
collate Latin1_General_CI_AS
go

use master;
go
create database testdb_bin
collate SQL_Latin1_General_CP850_BIN2
go

-- list all the binary collations available on the server:
SELECT Name, Description FROM fn_helpcollations() WHERE Name like '%bin2%'

Backup:

-- take a database backup
BACKUP DATABASE MyOptionsTest
   TO DISK = '/tmp/MyOptionsTest.bak'
     WITH FORMAT;
GO

-- restore a the backup to a different db
RESTORE DATABASE MOTest
   FROM DISK = '/tmp/MyOptionsTest.bak'
   WITH
     MOVE 'MyOptionsTest' TO '/var/opt/mssql/data/MOTest.mdf',
     MOVE 'MyOptionsTest_log' TO '/var/opt/mssql/data/MOTest_log.ldf'

use MyOptionsTest
go
delete from dbo.t1
select * from dbo.t1

-- restore backup to same database
use master;
go
RESTORE DATABASE MyOptionsTest
   FROM DISK = '/tmp/MyOptionsTest.bak'
   WITH REPLACE
GO

mssql-scripter:

2023 pip install mssql-scripter
2024 mssql-scripter -S localhost -d jira850 -U koki
2028 mssql-scripter -S localhost -d jira881 -U koki --schema-and-data > ./jira881_mssql_scripter_out.sql
2029 ls -ltr
2030 vim jira881_mssql_scripter_out.sql
2031 mssql-scripter -S localhost -d jira881 -U koki --schema-and-data > ./jira881_mssql_scripter_out.sql

mssql-scripter is exceptionally useful, it allowed me to export the whole database in text format, which enables me to do text manipulations using Unix filters on the data, also allows the use of tools like diff and GUI diffuse to compare exports done after certain application operations.
Very useful tool.