在 SSMS GUI 上查看 SQL Server 日誌的權限

我想讓使用者從 GUI 中查看 SQL Server 日誌，而不是通過 xp_readerrorlog。我只是想知道這是否可能？

謝謝你。

是的，這可以通過創建一個具有“ securityadmin”伺服器角色的使用者，然後如果需要，Deny他可以通過 LOGON 觸發器訪問 SQL Server 查詢視窗

USE [master]
       GO

   ----Create Login
   CREATE LOGIN [DBA_ErrorLogUser] 
   WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], 
   CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   GO

   --- Add server Add securityadmin   
   --  To view SQL Server error logs user must be a part of securityadmin server role
   EXEC master..sp_addsrvrolemember 
   @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
   GO

   --- Mapp user with login to grant access to logs
   CREATE USER [DBA_ErrorLogUser] FOR LOGIN [DBA_ErrorLogUser]
   GO

   --- Deny Alter to any Login 
   DENY ALTER ANY LOGIN TO DBA_ErrorLogUser 
   GO 

   --- Grant permission to view Sql Server Logs
   Grant EXECUTE ON master.sys.xp_readerrorlog TO DBA_ErrorLogUser 
   GO 


   --- Create a log on Trigger to deny access to Query Window
   IF EXISTS ( SELECT  *
               FROM    master.sys.server_triggers
               WHERE   parent_class_desc = 'SERVER'
                       AND name = N'Deny_QueryWindowLogin_Trigger' ) 
       DROP TRIGGER [Deny_QueryWindowLogin_Trigger] ON ALL SERVER
   GO

   Create TRIGGER Deny_QueryWindowLogin_Trigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

   DECLARE @data XML
   SET @data = EVENTDATA()

   DECLARE @AppName SYSNAME,
           @LoginName SYSNAME

   SELECT  @AppName = [program_name]
   FROM    sys.dm_exec_sessions
   WHERE   session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

   SELECT  @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

   IF @AppName= 'Microsoft SQL Server Management Studio - Query' AND @LoginName = 'DBA_ErrorLogUser' 
       BEGIN
           ROLLBACK ; --Disconnect the session

       END 
END ;

注意：必須閱讀 securityadmin 伺服器角色權限

引用自：https://dba.stackexchange.com/questions/82145