Azure SQL

 Azure SQL

1. SQL Server on Azure Virtual Machines
2. Azure SQL Database
3. Azure SQL Managed Instance (Azure SQL MI)
4. Azure SQL Edge
5. Azure Synapse Analytics - dedicated SQL Pool (formerly SQL DW)

Azure SQL Database

1.建立SQL Server resource時選擇驗證模式

• SQL authentication
• Azure Active Directory Authentication
• Both SQL and Azure Active Directory Authentication

2.設定Active Directory Admin 

可以在Resource建立時設定

設定一個Azure Directory User Account成為SQL Server管理員

只能設定一位

3.建立一個SQL驗證的SQL Server管理員帳號與密碼

4.建立帳號SQL Account for Developer or App

• 擁有Login帳號的登入方式
• 沒有Login帳號的登入方式 (Contained DB user) - SQL Authentication
• 沒有Login帳號的登入方式 (Contained DB user) - AD Authentication

(1)擁有Login帳號的登入方式

-- create SQL auth login from master 

USE [master]

CREATE LOGIN [User1] 

WITH PASSWORD = 'SuperSecret!' 

-- create a user mapped to a login in user db

USE [MyDB]

CREATE USER [User1] 

FOR LOGIN [User1]  

WITH DEFAULT_SCHEMA = dbo; 

  

-- add user to role(s) in YourDb

EXEC sp_addrolemember 'db_owner', 'User1';

EXEC sp_addrolemember 'db_datawriter', 'User1';

EXEC sp_addrolemember 'db_datareader’, 'User1';

(2)沒有Login帳號的登入方式 (Contained DB user) - SQL Authentication

-- create a contained user in user YourDb

USE [MyDB]

CREATE USER [User1] 

WITH PASSWORD = 'SuperSecret!', 

DEFAULT_SCHEMA = dbo; 

-- add user to role(s) in UserDb

EXEC sp_addrolemember 'db_owner', 'User1';

EXEC sp_addrolemember 'db_datawriter', 'User1';

EXEC sp_addrolemember 'db_datareader’, 'User1';

(3)沒有Login帳號的登入方式 (Contained DB user) - AD Authentication

-- add contained Azure AD user 

USE [MyDB]

CREATE USER [name@domain.com] 

FROM EXTERNAL PROVIDER 

WITH DEFAULT_SCHEMA = dbo;  

  

-- add user to role(s) in YourDb 

EXEC sp_addrolemember 'db_owner', 'name@domain.com';

EXEC sp_addrolemember 'db_datawriter', 'name@domain.com';

EXEC sp_addrolemember 'db_datareader’, 'name@domain.com';

5.建立帳號SQL Account for 受限制的DB管理員

-- create login  

USE [master]

CREATE LOGIN [dbadmin] 

WITH PASSWORD='SuperSecret!';  

-- create a user for login

CREATE USER [dbadmin] 

FOR LOGIN [dbadmin] ;

-- add user to Database-level role

-- 管理資料庫權限，可以建立刪除使用者資料庫

ALTER ROLE dbmanager ADD MEMBER [dbadmin]; 

-- 管理登入帳號，可以建立刪除登入帳號

ALTER ROLE loginmanager ADD MEMBER [dbadmin]; 

SPECIAL ROLES FOR SQL DATABASE AND AZURE SYNAPSE

Role name	Description
dbmanager	Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own.
db_exporter	Applies only to Azure Synapse Analytics dedicated SQL pools (formerly SQL DW). Members of the db_exporter fixed database role can perform all data export activities. Permissions granted via this role are CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT.
loginmanager	Can create and delete logins in the virtual master database.

Reference:

Configure and manage Azure AD authentication with Azure SQL

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell

Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics

https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

CREATE LOGIN (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql

Database-level roles

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

授予Schema權限，則該使用者只能對此schema能夠建立修改與刪除資料庫物件

授權物件給使用者

C. Setting the owner of a schema

CREATE SCHEMA Production AUTHORIZATION [Contoso\Mary];  

GO

CREATE SCHEMA (Transact-SQL)

C. Transfer ownership of a schema to a user

ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;

GO

ALTER AUTHORIZATION (Transact-SQL)

Reference:

Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics

https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql