SQLServer之创建LOGON触发器

logon触发器工作原理

登录触发器将为响应 logon 事件而激发存储过程。 与 sql server实例建立用户会话时将引发此事件。 登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。 因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 print 语句的消息）会传送到 sql server 错误日志。 如果身份验证失败，将不激发登录触发器。

logon触发器创建

语法：

--登录触发器在服务器对象-》触发器中查看

--声明数据库引用
use 数据库名;
go

--判断是否存在登录触发器，如果存在则删除
if exists(select * from sys.server_triggers where name=触发器名)
drop trigger 触发器名 on all server;
go

--创建触发器
create

--触发器标识符
trigger

--触发器名称
触发器名

on

--适用范围： sql server 2008 到 sql server 2017。
--将 ddl 或登录触发器的作用域应用于当前服务器。 如果指定了此参数，则只要当前服务器中的任何位置出现 event_type 或 event_group，就会激发该触发器。
all server

with

--适用范围： sql server 2008 到 sql server 2017。
--对 create trigger 语句的文本进行模糊处理。 使用 with encryption 可以防止将触发器作为 sql server 复制的一部分进行发布。 不能为 clr 触发器指定 with encryption。
encryption,

--execute as clause
--{ exec | execute } as { self | owner | 'user_name' }
--caller
--指定模块内的语句在模块调用方的上下文中执行。 执行模块的用户不仅必须对模块本身拥有适当的权限，还要对模块引用的任何数据库对象拥有适当权限。
--caller 是除队列外的所有模块的默认值，与 sql server 2005 行为相同。
--caller 不能在 create queue 或 alter queue 语句中指定。
--self
--execute as self 与 execute as user_name 等价，其中指定用户是创建或更改模块的用户。 创建或更改模块的用户的实际用户 id 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。
--self 是队列的默认值。
--owner
--指定模块内的语句在模块的当前所有者上下文中执行。 如果模块没有指定的所有者，则使用模块架构的所有者。 不能为 ddl 或登录触发器指定 owner。
--' user_name '
--指定模块内的语句在 user_name 指定的用户的上下文中执行。 将根据 user_name 来验证对模块内任意对象的权限。 不能为具有服务器作用域的 ddl 触发器或登录触发器指定 user_name。 请改用 login_name。
--user_name 必须存在于当前数据库中，并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置帐户，如 nt authority\localservice、nt authority\networkservice 或 nt authority\localsystem。
--执行上下文的用户 id 存储在元数据中，可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
execute as 'username'

--for | after
--after 指定 dml 触发器仅在触发 sql 语句中指定的所有操作都已成功执行时才被触发。 所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
--如果仅指定 for 关键字，则 after 为默认值。
--不能对视图定义 after 触发器。
{ for | after }

--登录触发器标识符
logon

as
begin
sql_statement;
end

示例：本示例演示服务器只允许登录三个人，超过第三个人不允许登录。

--登录触发器在服务器对象-》触发器中查看

--声明数据库引用
use testss;
go

--判断是否存在登录触发器，如果存在则删除
if exists(select * from sys.server_triggers where name='logintri')
drop trigger logintri on all server;
go

--创建触发器
create

--触发器标识符
trigger

--触发器名称
logintri

on

--适用范围： sql server 2008 到 sql server 2017。
--将 ddl 或登录触发器的作用域应用于当前服务器。 如果指定了此参数，则只要当前服务器中的任何位置出现 event_type 或 event_group，就会激发该触发器。
all server

with

--适用范围： sql server 2008 到 sql server 2017。
--对 create trigger 语句的文本进行模糊处理。 使用 with encryption 可以防止将触发器作为 sql server 复制的一部分进行发布。 不能为 clr 触发器指定 with encryption。
encryption,

--execute as clause
--{ exec | execute } as { self | owner | 'user_name' }
--caller
--指定模块内的语句在模块调用方的上下文中执行。 执行模块的用户不仅必须对模块本身拥有适当的权限，还要对模块引用的任何数据库对象拥有适当权限。
--caller 是除队列外的所有模块的默认值，与 sql server 2005 行为相同。
--caller 不能在 create queue 或 alter queue 语句中指定。
--self
--execute as self 与 execute as user_name 等价，其中指定用户是创建或更改模块的用户。 创建或更改模块的用户的实际用户 id 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。
--self 是队列的默认值。
--owner
--指定模块内的语句在模块的当前所有者上下文中执行。 如果模块没有指定的所有者，则使用模块架构的所有者。 不能为 ddl 或登录触发器指定 owner。
--' user_name '
--指定模块内的语句在 user_name 指定的用户的上下文中执行。 将根据 user_name 来验证对模块内任意对象的权限。 不能为具有服务器作用域的 ddl 触发器或登录触发器指定 user_name。 请改用 login_name。
--user_name 必须存在于当前数据库中，并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置帐户，如 nt authority\localservice、nt authority\networkservice 或 nt authority\localsystem。
--执行上下文的用户 id 存储在元数据中，可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
execute as 登录名

--for | after
--after 指定 dml 触发器仅在触发 sql 语句中指定的所有操作都已成功执行时才被触发。 所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
--如果仅指定 for 关键字，则 after 为默认值。
--不能对视图定义 after 触发器。
for

--登录触发器标识符
logon
as
begin
if (select count(1) from sys.dm_exec_sessions where is_user_process=1 and original_login_name=登录名) >3 and original_login()=登录名
begin
rollback;
end
end

示例结果：

logon登录触发器优缺点

优点：

1、可以使用登录触发器来审核和控制服务器会话。

2、通过跟踪登录活动、限制 sql server的登录名或限制特定登录名的会话数。

3、防止数据库账号密码泄露或者不合法的ip登录。

缺点：

1、登录可能引起不必要的触发逻辑。

2、登录触发器创建的会话是实例级别，并且应用于所有服务器，可能会对新建数据库和会话产生不必要的影响。