如何讓登入（Login）連接SQL Server時，在物件總管只看的到有權限的資料庫

2014-04-24

7101
0
TSQL
2014-07-04

說明

好友饅頭昨天跟筆者討論，想要讓特定的SQL Server登入（Login）只看的到特定的資料庫，一般狀況下若您建立登入之後，預設該登入就有權限連接到SQL Server執行個體，並且具有檢視任何資料庫中繼資料的權限，您可以用下列的T-SQL來驗證，當您建立登入之後，透過HAS_PERMS_BY_NAME安全性函數來查看其VIEW ANY DATABASE與CONNECT SQL的權限，可以得到回傳值為1，代表該登入具有上述權限。


--建立登入，名稱為l1
IF EXISTS (SELECT * FROM sys.server_principals sp  WHERE name = 'l1')
   DROP LOGIN l1
GO

CREATE LOGIN l1 WITH PASSWORD = 'P@ssw0rd'
GO

--檢視l1是否有檢視任何資料庫及連接SQL的權限
EXECUTE AS Login = 'l1'
SELECT SUSER_NAME() LoginName,USER_NAME() DBUserName
			,HAS_PERMS_BY_NAME(null,null,'VIEW ANY DATABASE') HasViewAnyDB
			,HAS_PERMS_BY_NAME(null,null,'CONNECT SQL') HasConnectSQL
REVERT

也因為具備VIEW ANY DATABASE的權限，不論是否有權限可以連接到特定資料庫，透過SSMS連接到SQL Server執行個體之後，就可以在物件總管中看到所有的系統資料庫及使用者資料庫（如下圖所示）。

若您想要讓使用者只看的到上圖的DB1和DB2，該怎麼做呢？請見下一節的說明。

建立測試資料庫

您可以利用下列T-SQL指令碼來建立測試資料庫：


--建立測試資料庫，DB1和DB2
use master
go

IF EXISTS (SELECT * FROM sys.databases d  WHERE name = 'DB1')
    DROP DATABASE DB1
GO

CREATE DATABASE DB1
  GO

IF EXISTS (SELECT * FROM sys.databases d  WHERE name = 'DB2')
    DROP DATABASE DB2
GO

CREATE DATABASE DB2
GO

建立登入

接著建立SQL Server驗證的登入，如下列的T-SQL，示範建立一個名稱為l的登入。


--建立登入，名稱為l
IF EXISTS (SELECT * FROM sys.server_principals sp  WHERE name = 'l')
   DROP LOGIN l
GO

CREATE LOGIN l WITH PASSWORD = 'P@ssw0rd'
GO

接著使用l來連接SQL Server，您將看到如下圖的樣子。

變更資料庫擁有者

預設資料庫擁有者為建立資料庫所使用的登入，若您想要讓登入l只看的到DB1和DB2，您必須變更資料庫的擁有者為登入l，您可以直接在下圖資料庫屬性的視窗中將擁有者改為l。

或是使用下列的T-SQL來進行相同的目的：


IF EXISTS (SELECT * FROM sys.databases d  WHERE name = 'DB1')
BEGIN
	USE DB1
	ALTER AUTHORIZATION ON DATABASE::DB1 to l
END

IF EXISTS (SELECT * FROM sys.databases d  WHERE name = 'DB2')
BEGIN
	USE DB2
	ALTER AUTHORIZATION ON DATABASE::DB2 to l
END

拒絕VIEW ANY DATABASE的權限

最後則是將登入l的VIEW ANY DATABASE權限拿掉，例如下列的T-SQL指令碼：


USE master
GO

DENY VIEW ANY DATABASE TO l
GO

完成上述步驟後您就可以讓登入l只看的到DB1和DB2。

補充說明

若您在變更資料庫擁有者時，可能發生如下圖代號為15110的錯誤。

此時您必須先將登入所對應的使用者刪除之後才能順利變更資料庫擁有者，例如下列T-SQL示範使用者u是對應到登入l，因此當使用者u存在於DB1時，您執行變更資料庫擁有者會失敗，所以必須先將該使用者移除。


IF EXISTS (SELECT * FROM sys.databases d  WHERE name = 'DB1')
  BEGIN
    USE DB1
    IF EXISTS (SELECT
        *
      FROM sys.database_principals dp
      WHERE name = 'u')
        DROP USER u  

	ALTER AUTHORIZATION ON DATABASE::DB1 to l

END