SQL Accounts

Cheat-cheet how to create and alter accounts on (Azure) SQL server and databases.

Account management

login: Must be created in master database. can be used to get permissions on multiple databases
user: is created in a database.

create a Login

CREATE LOGIN mydbuser WITH PASSWORD ='mysupersecretpassword';
GO

Create a User

CREATE USER mydbuser FROM LOGIN mydbuser;

Grant user permissions on database: (make sure you’re connected to the database)

ALTER ROLE [db_owner] add member mydbuser;

These roles are database-level roles that can be used to manage database security.

Role Name	Description
`db_owner`	Members of this role can perform any activity in the database.
`db_datareader`	Members of this role can read all data from all user tables in the database.
`db_datawriter`	Members of this role can add, modify, or delete data in all user tables in the database.
`db_ddladmin`	Members of this role can run any Data Definition Language (DDL) command in the database.
`db_securityadmin`	Members of this role can manage database security, including adding and removing database users and roles.
`db_accessadmin`	Members of this role can manage Windows groups and SQL Server logins that can access the database.
`db_backupoperator`	Members of this role can back up the database.
`db_restoreoperator`	Members of this role can restore the database.