SQLServer限制IP,限制用户,限制SSMS登录

SQL Server不像Mysql那样原生支持范围IP登录。

只是足行使Login触发器来兑现。

以下也运用Login触发器实现限制用户u_user_r在指定IP192.168.1.205使用SSMS登录的Login触发器脚本:

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() = 'u_user_r'
    AND (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(20)'))='192.168.1.205'
    )
     ROLLBACK;
END;

正文SQL Server链接:http://www.cnblogs.com/ajiangg/p/6480280.html

相关文章