MSSQL cheat sheet

README.md

List Users

select name as username,
       create_date,
       modify_date,
       type_desc as type,
       authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
      and sid is not null
      and name != 'guest'
order by username;

List Schemas

select s.name as schema_name, 
    s.schema_id,
    u.name as schema_owner
from sys.schemas s
    inner join sys.sysusers u
        on u.uid = s.principal_id
order by s.name;

Create a User with password and default Schema

First we create anew Schema named 'demo', then we create a new login called 'demo_app' with password authentication. then we create the user with the same name as the login 'demo_app'

USE [master];
GO

CREATE SCHEMA [demo];
GO

CREATE LOGIN demo_app WITH PASSWORD = 'demo_app_1234';
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'demo_app')
BEGIN
    CREATE USER [demo_app] FOR LOGIN [demo_app]
    EXEC sp_addrolemember N'db_owner', N'demo_app'
END;
GO

ALTER USER demo_app WITH DEFAULT_SCHEMA = demo;
GO