SQL Server中sys.syslogin中updatedate字段的浅析

q989

在系统视图sys.syslogins中，有createdate与updatedate两个字段，分别表示创建登录名与更新登录名的日期，如果你用updatedate的值来判断一个登录名的权限是否被修改过，那么你就要跳坑里了。当然sys.sysusers视图里面updatedate字段也不能作为某个user权限最后一次修改的依据，如下测试所示：

USE [master]

GO

CREATELOGIN [kerry] WITHPASSWORD=N'kerry123456',DEFAULT_DATABASE=[AdventureWorks2014],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF

GO

USE [AdventureWorks2014]

GO

CREATEUSER [kerry] FORLOGIN [kerry]

GO

USE [AdventureWorks2014]

GO

ALTERROLE [db_datareader] ADD MEMBER [kerry]

GO

USEmaster;

GO

SELECT  name ,

      createdate ,

        updatedate ,

      accdate ,

      dbname

FROM   sys.syslogins

WHERE  name ='kerry';

如下截图所示，sys.syslogins中，name为kerry的这条记录的createdate与updatedate字段值为2017-06-07 11:45:48:527，就是我创建该登录名的时间。

USE AdventureWorks2014;

GO

SELECT  name ,

      createdate ,

      updatedate

FROM   sys.sysusers

WHERE   name ='kerry';

sys.sysusers中的name为kerry的这条记录的createdate与updatedate字段值也是同一个值。如下所示：

我们修改kerry的权限，发现sys.syslogins和sys.sysusers中updatedate字段的值均没有变化

USE [AdventureWorks2014]

GO

ALTERROLE [db_datawriter] ADD MEMBER [kerry]

GO

USE [AdventureWorks2014]

GO

ALTERROLE [db_ddladmin] ADD MEMBER [kerry]

GO

USE AdventureWorks2014;

GO

SELECT  name ,

      createdate ,

      updatedate

FROM  sys.sysusers

WHERE  name ='kerry';

当然，如果你修改登录名（login）的SERVER ROLE权限，那么updatedate字段就会变化，如下测试所示

ALTERSERVERROLE [processadmin] ADD MEMBER [kerry]

GO

另外测试发现，启用或禁用DISABLE登录名，sys.syslogins中这个字段updatedate的值是会变化的

ALTERLOGIN [kerry] ENABLE

GO

ALTERLOGIN [kerry] DISABLE

GO

这里就不研究sys.sysusers在什么条件下会触发updatedate字段会更新。 那么如何查看某个账号的权限被变跟过呢？ 这个只能用触发器或开启审计，当然也可以用下面脚本，但是这个是有很强的时效性的。

DECLARE @tracefile VARCHAR(500)

-- Get path of default trace file

SELECT @tracefile =CAST(value AS VARCHAR(500))

FROM ::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1

AND property = 2

-- Get security changes from the default trace

SELECT*

FROM ::fn_trace_gettable(@tracefile,DEFAULT) trcdata -- DEFAULT means all trace files will be read

INNER JOIN sys.trace_events evt ON trcdata.EventClass = evt.trace_event_id

WHERE trcdata.EventClass IN(102, 103, 104, 105, 106, 108, 109, 110, 111)

ORDERBY trcdata.StartTime

                 --trcdata.DatabaseID

                 --trcdata.TargetLoginName

参考资料：

https://msdn.microsoft.com/zh-cn/library/ms178593.aspx

https://msdn.microsoft.com/zh-cn/library/ms179871.aspx