多人如何共同管理同一台 SQL Database 伺服器

本文將介紹多人如何共同管理同一台 SQL Database 伺服器。

情境說明

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

image

註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(如下圖)。

image

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

image

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

image

再來是執行下列的指令碼來建立名稱為 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

執行結果如下:

image

接著您就可以利用 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 的權限,執行結果如下:

image

參考資料

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

- 伺服器層級角色

- CREATE LOGIN (Windows Azure SQL Database)

- sp_addrolemember (Transact-SQL)