積沙成塔

【情境說明】

當您建立 SQL Database 伺服器時需要建立一個 Login ^註1 來管理伺服器（如下圖），這個 Login 的權限相當於內部部署（On-premise） SQL Server 中的 sa，相信大家都對 sa 不陌生，筆者就不再多做介紹，簡單來說，這個 Login 對伺服器擁有最大的權限，什麼都可以做，但是如果您想要讓多人同時管理同一 SQL Database 包含伺服器及資料庫，該如何進行？

註1：登入，在此所稱的登入是一名詞，表示用來連接至 SQL Server 或 SQL Database 伺服器的一個伺服器層級的使用者帳戶，因容易與動詞的登入混淆，因此本文使用英文 Login 來表示）來管理伺服器。

【實作方式】

SQL Database 上有兩個主要的資料庫角色，分別是【loginmanager】與【dbmanager】，說明如下：

loginmanager：相當於 On-premise SQL Server 的 securityadmin 固定伺服器角色，該角色的成員都有權限可以建立 Login。

dbmanager：相當於 On-premise  SQL Server 的 dbcreator。

接著我們就來模擬一個情況，假設您建立 SQL Database 伺服器後，想要讓另外一個使用者 Sky 可以利用 SkyLogin 這個 Login 來登入並管理 SQL Database。首先，建立 Login 和賦予 loginmanager 及 dbmanager 資料庫角色都必須在 master 系統資料庫中進行，然而 SQL Database 不支援使用【USE 資料庫名稱】來切換作用中的資料庫，若您使用 SQL Database 管理入口網站來管理 Login，請指定要連接的 Database 名稱為 master（如下圖）。

否則您會發現您輸入 T-SQL 後，上方的 Run 按鈕是反白的狀態，無法讓您執行您所寫的指令碼。

若您使用 SSMS 來管理 Login，請先選取物件總管中的 master 系統資料庫後在選擇新增查詢，以確保目前作用中的資料庫是在 master。

再來是執行下列的指令碼來建立名稱為 SkyLogin 的 Login ，並建立名稱為 SkyUser 的資料庫使用者，最後則是利用 sp_addrolemember 系統預存程序來將資料庫使用者加入 loginmanager 和 dbmanager 資料庫角色：

--建立 Login

CREATE LOGIN SkyLogin WITH PASSWORD='Biw9S/BLWpiAoHb0p)}0'

GO

--建立 User

CREATE USER SkyUser FOR LOGIN SkyLogin

GO

--將 SkyUser 加入 loginmanager 資料庫角色

EXEC sp_addrolemember 'loginmanager','SkyUser'

GO

--將 SkyUser 加入 dbmanager 資料庫角色

EXEC sp_addrolemember 'dbmanager','SkyUser'

GO

執行結果如下：

接著您就可以利用 SkyLogin 來登入 SQL Database 伺服器並且管理 Login 及資料庫，筆者利用下列的指令碼來測試 SkyLogin 的權限：

--建立 Login

CREATE LOGIN Login1 WITH PASSWORD='Biw9S/BLWpiAoHb0p)}0'

GO

--【注意】請利用另外一個足夠權限的User來賦予 SkyUser 管理 User 的權限，否則會會看到如下的錯誤訊息：

-- Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

--GRANT ALTER ANY USER  TO SkyUser

--建立 User

CREATE USER User1 FOR  LOGIN Login1

GO

--刪除 User

DROP USER User1

GO

--刪除 Login

DROP LOGIN Login1

GO

--查詢 SQL Database 擁有那些 login

SELECT *

FROM sys.sql_logins

GO

--建立資料庫

CREATE DATABASE MyDB2

GO

--查詢 SQL Database 已經建立那些資料庫

SELECT *

FROM sys.databases

GO

--刪除資料庫

DROP DATABASE MyDB2

GO

 

您可以看到有關 Login 以及資料庫的管理，都已經可以利用 SkyLogin 來完成，但其中管理 User 的部分因為都不屬於 loginmanager 或 dbmanager 資料庫角色，您必須利用 GRANT ALTER ANY User 的指令碼來賦予管理 User 的權限，執行結果如下：

【參考資料】

- 管理 Windows Azure SQL Database 中的資料庫和登入

- 伺服器層級角色

- CREATE LOGIN (Windows Azure SQL Database)

- sp_addrolemember (Transact-SQL)