SQL Server权限管理专题(持续更新)

作者: 这货不是王马勺 | 来源:发表于2022-01-27 11:11 被阅读0次

创建服务器对象触发器
限制某个账号（某个ip）通过SSMS方式连库

CREATE TRIGGER [tr_connection_limit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (
    EXISTS(
            SELECT 1 FROM sys.dm_exec_sessions 
            WHERE 
                session_id = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'smallint') 
            AND program_name like 'Microsoft SQL Server Management Studio%'
            )
    AND ORIGINAL_LOGIN() = 'o_user'
    --AND (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(20)')) like '10.10.188.%'
    )
     ROLLBACK;
END;

做某账号连库记录

SELECT 
sp.login_time,sp.loginame,sp.hostname 
FROM 
[Master].[dbo].[SYSPROCESSES] sp
where sp.loginame = 'sa' 
and hostname <> ''
order by login_time desc