[MSSQL 2008] 建立有權限查看SQL Server Log之SQL login

2010-07-14

10969
0
MSSQL
2011-04-15

建立有權限查看SQL Server Log之SQL login

若要開放使用者透過UI介面查看SQL Server Log，需有SecurityAdmin權限。但以有SecurityAdmin權限的使用者登入後開啟SQL Server 記錄檔時將會有如下Error：

結構描述 'sys'，資料庫 'mssqlsystemresource'，物件 'xp_readerrorlog' 沒有 EXECUTE 權限。(錯誤:229)

必需再授與xp_readerrorlog權限後才能正常開啟SQL Server 記錄檔。可使用以下sql statement建立一個授與SQL Server記錄檔查看權限的使用者：

USE [master]

CREATE LOGIN [LogReader] WITH PASSWORD=N'passwd',

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER [LogReader] FOR LOGIN [LogReader]

EXEC master..sp_addsrvrolemember @loginame = N'LogReader', @rolename = N'securityadmin'

GRANT EXEC ON xp_readerrorlog TO LogReader

或不授與xp_readerrorlog權限，以T-SQL語法執行SQL Server預存之Stored Procedure查看log：

Exec sp_readerrorlog @p1 int = 0, @p2 int = NULL, @p3 varchar(255) = NULL, @p4 varchar(255) = NULL

可接4個參數，各參數說明如下:

(1)@p1 開啟目前或封存的log：0=current, 1=Archive#1, 2=Archive#2, …

(2)@p2 Log file type: 1 或null = error log, 2=SQL Agent log

(3)@p3 查詢字串1

(4)@p4 查詢字串2

2011/04/14 補充

SecurityAdmin角色可以管理SQL Login, 可GRANT、DENY 及 REVOKE server level或database level的權限，且可重設SQL Login的密碼。若只為了讓User能查看Error Log便授與SecurityAdmin角色似乎不太合理。因應這樣的需求，可以仿照master..sp_readerrorlog 再寫一支Stored Procedure，但不要強制使用者需要SecurityAdmin角色，再將此Stored Procedure的執行權限授與使用者。如此一來使用者不需要SecurityAdmin角色也可以查看SQL Error Log了。

Createprocsp_QueryErrorLog(
         @p1        int=0,
         @p2        int=NULL,
         @p3        varchar(255)=NULL,
         @p4        varchar(255)=NULL)
As
Begin

         /* 不要強制使用者需為SecurityAdmin
         if (not is_srvrolemember(N'securityadmin') = 1)
         begin
            raiserror(15003,-1,-1, N'securityadmin')
            return (1)
          end
          */

      if (@p2 isNULL)
            execsys.xp_readerrorlog @p1
        else
            execsys.xp_readerrorlog @p1,@p2,@p3,@p4
End
GO

-- 授與使用者執行sp_QueryErrorLog的權限
Grant exec on sp_QueryErrorLog to [User]

-- 執行sp_QueryErrorLog 查看logQueryErrorLogQueryErrorLog
Exec sp_QueryErrorLog @p1 int = 0, @p2 int = NULL, @p3 varchar(255) = NULL, @p4 varchar(255) = NULL

回首頁