监控-系统实例

原始脚本来自TG，自己对部分脚本做了调整，分享出来仅供参考，请勿整篇Copy！

使用以下语句获取【服务器硬件信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_SystemInfo]    脚本日期: 02/07/2017 17:36:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2014/05/20 14:27
    <中文名称>    获取服务器硬件信息
    <功能说明>    采集当前数据库服务器的详细硬件信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递服务器编号参数@ServerID
            　    注意事项：磁盘raid信息和资产编号信息暂无数据
    <调用示例>  exec DBA_Pro_Get_SystemInfo 1002
【修订记录】
-------------------------------------------------------------------
    <2014/07/03 15:48>  <DBA>: 修正 调用方式/注意事项 的文字描述 
    <2014/06/20 16:08>  <DBA>: 格式规范化 
    <2013/05/20 14:27>  <DBA>: 创建 
******************************************************************/
ALTER PROC [dbo].[DBA_Pro_Get_SystemInfo]
    @ServerID INT
AS
BEGIN
    DECLARE @HostName            varchar(64)    ,    --服务器名称
            @ServerType            varchar(128),    --服务器型号
            @CpuInfo            varchar(128),    --CPU信息
            @MemorySize            int            ,    --物理内存大小
            @HardDiskSize_Sum    int            ,    --硬盘容量
            @HardDiskType        varchar(128),    --硬盘类型
            @HardDiskNum        int            ,    --物理硬盘数量
            @MAC_1                varchar(32)    ,    --MAC地址1
            @MAC_2                varchar(32)    ,    --MAC地址2
            @MAC_3                varchar(32)    ,    --MAC地址3
            @BuyTime            datetime    ,    --购买时间
            @OS_Name            varchar(64)    ,    --操作系统名称
            @OS_Version            varchar(64)    ,    --操作系统版本号
            @OS_SetupTime        datetime    ,    --操作系统安装时间
            @IntranetIP            VARCHAR(15)    ,    --内网IP地址
            @InternetIP            varchar(15)    ,    --外网IP地址
            @SpareIP            varchar(15) ,    --电信IP(待修正)
            @LastRestartTime    datetime    ,    --最后重启时间
            @now                datetime

    SET NOCOUNT ON

    IF (@ServerID<1000 or @ServerID>9999)
    begin
        print '服务器编号参数错误！'
        return
    end

    BEGIN TRY

    set @now=GETDATE()

    --get systeminfo data
    if OBJECT_ID('tempdb..#tb_systeminfo')is not null
        drop table #tb_systeminfo
    create table #tb_systeminfo
    (
        id int identity(1,1),
        deviceinfo varchar(500)
    )
    insert into #tb_systeminfo
    exec xp_cmdshell 'systeminfo'

    --OS_Name
    select @OS_Name=SUBSTRING(deviceinfo,charindex('Microsoft',deviceinfo),LEN(deviceinfo)-charindex('Microsoft',deviceinfo)+1) 
    from #tb_systeminfo 
    where deviceinfo like '%OS 名称:%'

    --OS_Version
    select @OS_Version=ltrim(right(deviceinfo,len(deviceinfo)-LEN('OS 版本:')))
    from #tb_systeminfo 
    where deviceinfo like 'OS 版本:%'
    
    --HostName
    select @HostName=RTRIM(LTRIM(SUBSTRING(deviceinfo,(LEN('主机名:')+1),(len(deviceinfo)-len('主机名:'))))) 
    from 
        #tb_systeminfo 
    where deviceinfo like '%主机名%'

    --servertype
    SELECT @ServerType=LTRIM(REPLACE(SUBSTRING(deviceinfo,(LEN('系统制造商:')+1),(len(deviceinfo)-len('系统制造商:'))),'Inc.',''))+' '+
        (select RTRIM(LTRIM(SUBSTRING(deviceinfo,(LEN('系统型号:')+1),(len(deviceinfo)-len('系统型号:'))))) from #tb_systeminfo where deviceinfo like '%系统型号:%')+' '+
        (select rtrim(ltrim(substring(deviceinfo,CHARINDEX(':',deviceinfo)+1,(LEN(deviceinfo)-CHARINDEX(':',deviceinfo))))) from #tb_systeminfo where deviceinfo like '%系统类型:%')
    from 
        #tb_systeminfo 
    where deviceinfo like '%系统制造商:%'

    --cpuinfo
    declare @id int,
            @a int,
            @b int
    select @id=id,@cpuinfo=LTRIM(SUBSTRING(deviceinfo,(LEN('处理器:')+1),(len(deviceinfo)-len('处理器:'))))+'处理器类型为：'
    from 
        #tb_systeminfo 
    where deviceinfo like '%处理器:           安装了%'

    set @id=@id+1

    --LastRestartTime
    if object_id('tempdb..#temp_restart')is not null
        drop table #temp_restart

    create table #temp_restart(id int identity(1,1),info varchar(500))

    insert into #temp_restart(info)
    exec xp_cmdshell 'net statistics workstation'

    select @LastRestartTime=CONVERT(VARCHAR(16),CONVERT(DATETIME,ltrim(replace(info,'统计数据开始于',''))),120)+':00.000'
    from 
        #temp_restart 
    where info like '统计数据开始于%'

    --get CpuTypeInfo
    if OBJECT_ID('tempdb..#tb_cpuinfo')is not null
        drop table #tb_cpuinfo
    create table #tb_cpuinfo
    (
        id int identity(1,1),
        cpuinfo varchar(128)
    )
    insert into #tb_cpuinfo
    select distinct right(deviceinfo,len(deviceinfo)-CHARINDEX(':',deviceinfo))
    from 
        #tb_systeminfo 
    where id>=@id and id<@id+2

    select @a=1
    select @b=COUNT(1) from #tb_cpuinfo
    
    while @a<=@b
    begin
        select @cpuinfo=@cpuinfo+' '+cpuinfo+' '
        from #tb_cpuinfo
        where id=@a
        
        set @a=@a+1
    end

    --MemorySize
    SELECT @MemorySize=replace(replace(rtrim(LTRIM(SUBSTRING(deviceinfo,(LEN('物理内存总量:     ')+1),(len(deviceinfo)-len('物理内存总量:     '))))),'MB',''),',','')
    from 
        #tb_systeminfo
    where deviceinfo like '%物理内存总量:%'

    --OS_SetupTime
    select @OS_SetupTime=cast(replace(RTRIM(LTRIM(SUBSTRING(deviceinfo,(LEN('初始安装日期:     ')+1),(len(deviceinfo)-len('初始安装日期:     '))))),',','') as datetime)
    from 
        #tb_systeminfo 
    where deviceinfo like '%初始安装日期:%'

    --buytime
    select @buytime=cast(replace(RTRIM(LTRIM(SUBSTRING(deviceinfo,(LEN('初始安装日期:     ')+1),(len(deviceinfo)-len('初始安装日期:     '))))),',','') as datetime)
    from 
        #tb_systeminfo 
    where deviceinfo like '%初始安装日期:%'

    --HardDiskNum
    truncate table #tb_systeminfo
    insert into #tb_systeminfo
    exec xp_cmdshell 'wmic DISKDRIVE get Caption,size,InterfaceType'

    select @HardDiskNum=COUNT(1) 
    from 
        #tb_systeminfo 
    where id<>1 and deviceinfo is not null and LEN(deviceinfo)>1 AND deviceinfo NOT LIKE '%USB%'

     --HardDiskType
    TRUNCATE table #tb_systeminfo
    insert into #tb_systeminfo
    exec xp_cmdshell 'wmic DISKDRIVE get Caption'

    --排除硬盘型号相同数据
    ;with leno as
    (
        select *,ROW_NUMBER()over(PARTITION by deviceinfo order by deviceinfo)cwt from #tb_systeminfo
    )
    delete from leno where cwt>1

    set @HardDiskType=''

    select @HardDiskType=@HardDiskType+deviceinfo+';'
    from 
        #tb_systeminfo  
    where id<>1 and deviceinfo is not null and LEN(deviceinfo)>1 AND deviceinfo NOT LIKE '%USB%'

    select @HardDiskType=left(@HardDiskType,LEN(@HardDiskType)-1)

    --HardDiskSize_Sum
    TRUNCATE table #tb_systeminfo
    insert into #tb_systeminfo
    exec xp_cmdshell 'wmic DISKDRIVE get size'

    select @HardDiskSize_Sum=sum(cast(replace(REPLACE(deviceinfo,CHAR(13),''),' ','') as bigint)*1.0/(1024*1024))
    from 
        #tb_systeminfo
    where id<>1 and deviceinfo is not null and LEN(deviceinfo)>1 AND deviceinfo NOT LIKE '%USB%'

    --HardDiskSize_Logical --Add 2016-11-15
    TRUNCATE table #tb_systeminfo
    insert into #tb_systeminfo    
    exec xp_cmdshell 'wmic LogicalDisk get Description,Caption,Size'

    DELETE FROM Info_DiskSpace WHERE ServerID=@ServerID
    INSERT INTO [Info_DiskSpace]([ServerID],[DriveLetter],[TotalSize])
    select @ServerID,LEFT(deviceinfo,1)
    ,cast(replace(replace(substring(deviceinfo,charindex('disk',deviceinfo)+4,len(deviceinfo)),char(13),''),' ','') as bigint)/(1024*1024)   
    FROM  #tb_systeminfo
    where id<>1 and deviceinfo is not null and LEN(deviceinfo)>1 AND deviceinfo NOT LIKE '%USB%'
    and deviceinfo NOT LIKE '%CD-ROM%'

    --RaidInfo

    --MAC_1,MAC_2,MAC_3
    truncate  table #tb_systeminfo
    insert into #tb_systeminfo
    exec master..xp_cmdshell 'ipconfig /all'

    IF OBJECT_ID('tempdb..#tb_mac')is not null
        drop table #tb_mac
    create table #tb_mac(id int identity(1,1),mac varchar(32))

    IF (@OS_Name like '%2003%')
        insert into #tb_mac(mac)
        select ltrim(stuff(deviceinfo,1,charindex( ': ',deviceinfo), ' '))mac
        from 
            #tb_systeminfo 
        where deviceinfo like '%Physical Address%' and deviceinfo not like '%00-00-00%'
        order by mac
    ELSE
        insert into #tb_mac(mac)
        select ltrim(stuff(deviceinfo,1,charindex( ': ',deviceinfo), ' '))mac
        from 
            #tb_systeminfo 
        where deviceinfo like '%物理地址%' and deviceinfo not like '%00-00-00%'
        order by mac

    select @MAC_1= mac from #tb_mac where id=1
    select @MAC_2= mac from #tb_mac where id=2
    select @MAC_3= mac from #tb_mac where id=3

    --IP地址
    if OBJECT_ID('tempdb..#tb_IpInfo')is not null
        drop table #tb_IpInfo
    CREATE TABLE #tb_IpInfo(id int identity(1,1),ip varchar(15))

    IF (@OS_Name like '%2003%')
        insert into #tb_IpInfo(ip)
        select rtrim(ltrim(replace(stuff(deviceinfo,1,charindex( ':',deviceinfo), ''),CHAR(13),'')))ip
        from 
            #tb_systeminfo 
        where deviceinfo like '%IP Address%'
        order by ip
    ELSE
        insert into #tb_IpInfo(ip)
        select replace(replace(replace(substring(deviceinfo,charindex(':',deviceinfo)+1,len(deviceinfo)-charindex(':',deviceinfo)),'(首选)',''),char(13),''),' ','')ip
        from 
            #tb_systeminfo 
        where deviceinfo like '%IPv4 地址%'
        order by ip

    select @IntranetIP    = ip from #tb_IpInfo where id=1
    select @InternetIP    = ip from #tb_IpInfo where id=2
    select @SpareIP        = ip from #tb_IpInfo where id=3

    delete from Info_Hardware where ServerID=@ServerID

    insert into Info_Hardware(ServerID,DeviceID,HostName,ServerType,CpuInfo,MemorySize, 
                            HardDiskSize_Sum,HardDiskType,RaidInfo,HardDiskNum,MAC_1,MAC_2, 
                            MAC_3,BuyTime,Buyer,MachineRoomID,OS_Name,OS_Version, 
                            OS_SetupTime,IntranetIP,InternetIP,SpareIP,InsertTime,LastRestartTime)
    select @ServerID[ServerID],''[DeviceID],@HostName[HostName],@ServerType[ServerType],@CpuInfo[CpuInfo],
            @MemorySize[MemorySize],@HardDiskSize_Sum[HardDiskSize_Sum],@HardDiskType[HardDiskType],''[RaidInfo],
            @HardDiskNum[HardDiskNum],@MAC_1[MAC_1],@MAC_2[MAC_2],@MAC_3[MAC_3],@BuyTime[BuyTime],''[Buyer],
            0[MachineRoomID],@OS_Name[OS_Name],@OS_Version[OS_Version],@OS_SetupTime[OS_SetupTime],
            @IntranetIP[IntranetIP],@InternetIP[InternetIP],@SpareIP[SpareIP],@now[InsertTime],@LastRestartTime[LastRestartTime]

    if OBJECT_ID('tempdb..#tb_systeminfo')is not null
        drop table #tb_systeminfo
    if OBJECT_ID('tempdb..#tb_cpuinfo')is not null
        drop table #tb_cpuinfo
    if OBJECT_ID('tempdb..#tb_IpInfo')is not null
        drop table #tb_IpInfo
    IF OBJECT_ID('tempdb..#tb_mac')is not null
        drop table #tb_mac
    IF OBJECT_ID('tempdb..#temp_restart')is not null
        drop table #temp_restart
    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @ServerID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

中间添加的HardDiskSize_Logical用于获取逻辑磁盘信息，原本是在【磁盘空间】中，为了方便(xp_cmdshell)将其添加到获取硬件信息中。
使用以下语句获取【实例信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_InstanceInfo]    脚本日期: 02/07/2017 17:43:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2014/05/20 14:27
    <中文名称>    获取SQL实例信息
    <功能说明>    采集当前SQL实例详细信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：暂无
    <调用示例>  exec DBA_Pro_Get_InstanceInfo 100233
【修订记录】
-------------------------------------------------------------------
    <2014/06/20 16:08>  <DBA>: 格式规范化 
    <2013/05/20 14:27>  <DBA>: 创建 
******************************************************************/
ALTER PROC [dbo].[DBA_Pro_Get_InstanceInfo]
    @InstanceID INT
AS
BEGIN
    declare @ServerID            INT            ,    --服务器编号
            @HostName            varchar(32)    ,    --服务器名称
            @InstanceName        varchar(32)    ,    --实例名称
            @CreateTime            datetime    ,    --实例创建时间(未取数)
            @Edition            varchar(32)    ,    --产品版本类别
            @ProductVersion        varchar(64)    ,    --版本
            @ProductLevel        varchar(16)    ,    --SP级别
            @Language            varchar(16)    ,    --默认语言
            @Collation_Name        varchar(32)    ,    --默认排序规则
            @UserMaxConnections    int            ,    --用户最大连接数
            @BackupOption        varchar(32)    ,    --备份选项
            @RemoteTimeOut        int            ,    --远程查询超时值
            @MinMemory            int            ,    --实例设置最小内存
            @MaxMemory            int            ,    --实例设置最大内存
            @NetPort            int            ,    --实例端口
            @RecordTime            datetime    ,    --记录时间
            @UpdateTime            datetime    ,    --更新时间
            @RegeditKey            varchar(200),    --注册表键值
            @master                varchar(200)    --master.mdf文件物理路径

    SET NOCOUNT ON

    IF (@InstanceID<100000 or @InstanceID>999999)
    begin
        print '数据库实例编号参数错误！'
        return
    end

    BEGIN TRY

    SELECT    @HostName            =    CAST(HOST_NAME() AS VARCHAR(32))
           ,@ServerID            =    CAST(LEFT(CAST(@InstanceID AS VARCHAR(6)),4) AS INT)
           ,@InstanceName        =    CAST(ISNULL(SERVERPROPERTY ('InstanceName'),'MSSQLSERVER') AS VARCHAR(32))
           ,@Edition            =    CAST(SERVERPROPERTY('edition') AS VARCHAR(32))
           ,@productversion        =    CAST(SERVERPROPERTY('productversion') AS VARCHAR(64))
           ,@productlevel        =    CAST(SERVERPROPERTY('productlevel') AS VARCHAR(16))
           ,@Collation_Name        =    CAST(SERVERPROPERTY('Collation') AS VARCHAR(32))
           ,@Language            =    CAST(@@LANGUAGE AS VARCHAR(16))

    SELECT @MinMemory            =    value FROM master..SYSCONFIGURES WHERE config=1543
    SELECT @MaxMemory            =    value FROM master..SYSCONFIGURES WHERE config=1544
    SELECT @RemoteTimeOut        =    value FROM master..SYSCONFIGURES WHERE config=1520
    SELECT @UserMaxConnections    =    value FROM master..SYSCONFIGURES WHERE config=103

    --MSSQL Port(操作系统、数据库版本不一样，键值TcpPort所在位置不同，最好是到服务器上确认)
    declare @port varchar(20)
    --2005
    select @RegeditKey='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPALL'
    --2008r2
    --select @RegeditKey='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\Tcp\IPALL'
    exec master..xp_regread @rootkey='HKEY_LOCAL_MACHINE'
                            ,@key=@RegeditKey
                            ,@value_name='TcpPort'
                            ,@value=@port OUTPUT
    select @NetPort=CAST(@port as int)

    SELECT @BackupOption=(CASE WHEN value=1 THEN 'EnableCompression' ELSE 'DisableCompression' END)
    FROM 
        master.dbo.sysconfigures
    where config=1579
    /* alter 2016/07/25
    --get instance createtime
    if OBJECT_ID('tempdb..#tb_systeminfo')is not null
        drop table #tb_systeminfo
    create table #tb_systeminfo
    (
        id int identity(1,1),
        deviceinfo varchar(500)
    )

    select @master=physical_name from master.sys.database_files where file_id=1
    set @master='xp_cmdshell '+''''+'dir "'+@master+'"'+''+' /tc'''

    insert into #tb_systeminfo
    exec(@master)

    select TOP (1) @CreateTime=cast(replace(LEFT(deviceinfo,17),'  ',' ') as datetime) 
    from 
        #tb_systeminfo
    where deviceinfo like '%master.mdf%'-- and deviceinfo like '%/%' */

    delete from Info_Instance where InstanceID=@InstanceID

    insert into Info_Instance(InstanceID,Serverid,HostName,InstanceName,CreateTime,Edition,ProductVersion, 
                                ProductLevel,[Language],Collation_Name,UserMaxConnections,BackupOption, 
                                RemoteTimeOut,MinMemory,MaxMemory,NetPort,InsertTime)
    select @InstanceID[InstanceID],@serverid[serverid],@HostName[ServerName],@InstanceName[InstanceName],
            @CreateTime[createtime],@Edition[Edition],@ProductVersion[ProductVersion],@ProductLevel[ProductLevel],
            @Language[Language],@Collation_Name[Collation_Name],@UserMaxConnections[UserMaxConnections],@BackupOption[BackupOption],
            @RemoteTimeOut[RemoteTimeOut],@MinMemory[MinMemory],@MaxMemory[MaxMemory],@NetPort[NetPort],GETDATE()[InsertTime]

    if OBJECT_ID('tempdb..#tb_systeminfo')is not null
        drop table #tb_systeminfo

    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

原语句取master.mdf文件的创建时间作为实例的创建时间，暂时注释

dir D:\SQL08R2\MSSQL10_50.SQL08R2\MSSQL\DATA\master.mdf /tc

使用以下语句获取【实例登录名信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Login_LoginsInfo]    脚本日期: 02/07/2017 17:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2014/05/16 14:44
    <中文名称>    采集SQL实例登录名信息
    <功能说明>    采集当前实例通过SQL Server身份验证登录的登录名信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID；登录名严格按照创建->禁用->删除的步骤，各步骤间的时间间隔大于作业运行间隔
            　    注意事项：Login_LoginsInfo.LoginsID必须唯一且保持不变，过程中借助临时表，Login_LoginsInfo表只insert+update
                因此，对于有登录名删除的情况，Login_LoginsInfo表中字段IsDeleted标记为1
    <调用示例>  exec DBA_Pro_Login_LoginsInfo 100233
【修订记录】
-------------------------------------------------------------------
    <2014/12/25 15:48>  <DBA>: 登录名信息表增加字段：IsDeleted(是否已删除),用于标记登录名被删除
    <2014/09/01 15:27>  <DBA>: 登录名id溢出，登录名信息表、登录名密码变更日志表 字段[LoginsID]由int->bigint
    <2014/08/06 17:39>  <DBA>: 登录名信息表增加字段：IsLocked(是否登录已锁定)、DaysUntilExpiration(密码到期剩余天数)，过程中对应增加对这两个字段的采集
    <2014/07/17 13:39>  <DBA>: 受权限影响，将采集的信息保存在各实例上的数据表中，再由ETL登录名从表中获取数据
    <2014/05/16 14:44>  <DBA>: 创建
******************************************************************/
ALTER PROC [dbo].[DBA_Pro_Login_LoginsInfo]
@InstanceID BIGINT --2014/09/01 15:27 int->bigint
AS
BEGIN
    IF @InstanceID < 100101
       OR @InstanceID > 999999
    BEGIN
        PRINT('输入的实例ID有误，请核对！')
        RETURN
    END
    
    BEGIN TRY
        --创建临时表保存登录信息
        IF OBJECT_ID('tempdb..#LoginsInfo')IS NOT NULL
            DROP TABLE #LoginsInfo
        
        CREATE TABLE #LoginsInfo
        (
            [LoginsID]                [int] IDENTITY(1 ,1) NOT NULL    --登录名ID
           ,[InstanceID]              [int] NOT NULL    --实例ID
           ,[LoginName]               [nvarchar](64) NOT NULL    --登录名称
           ,[CreateDate]              [datetime] NOT NULL    --创建时间
           ,[PasswordLastSetTime]     [datetime] NOT NULL    --密码最后修改时间
           ,[IsSystemUser]            [bit] NOT NULL    --是否系统用户
           ,[IsDisabled]              [bit] NOT NULL    --是否禁用
           ,[IsPolicyChecked]         [bit] NOT NULL    --是否强制密码策略
           ,[IsExpirationChecked]     [bit] NOT NULL    --是否强制密码过期
           ,[IsLocked]                [bit] NOT NULL    --是否登录已锁定
           ,[DaysUntilExpiration]     [int] NULL    --密码到期剩余天数
           ,[PossibleDisableTime]     [datetime] NULL    --参考禁用时间(为空则在用)
           ,[UpdateTime]              [datetime] NULL DEFAULT(GETDATE())--信息更新时间
        ) 
        INSERT INTO #LoginsInfo
          (
            InstanceID
           ,LoginName
           ,CreateDate
           ,PasswordLastSetTime
           ,IsSystemUser
           ,IsDisabled
           ,IsPolicyChecked
           ,IsExpirationChecked
           ,IsLocked
           ,DaysUntilExpiration
           ,PossibleDisableTime
          )
        SELECT @InstanceID InstanceID
              ,slg.name
              ,s_lg.create_date
              ,PasswordLastSetTime = CONVERT(DATETIME ,LOGINPROPERTY(s_lg.name ,N'PasswordLastSetTime'))
              ,CASE 
                    WHEN s_lg.principal_id <= 263 THEN 1
                    ELSE 0
               END  AS IsSystemUser
              ,s_lg.is_disabled
              ,s_lg.is_policy_checked
              ,s_lg.is_expiration_checked
              ,IsLocked = CONVERT(BIT ,LOGINPROPERTY(s_lg.name , N'IsLocked'))
              ,DaysUntilExpiration = CONVERT(INT ,LOGINPROPERTY(s_lg.name , N'DaysUntilExpiration'))
              ,CASE 
                    WHEN s_lg.is_disabled = 1 THEN s_lg.modify_date
                    ELSE NULL
               END  AS PossibleDisableTime
        FROM   MASTER.sys.syslogins slg
               INNER JOIN MASTER.sys.sql_logins s_lg
                    ON  slg.name = s_lg.name
        ORDER BY
               s_lg.create_date
        
        --Step1 新添加(或首次执行)的登录名信息插入到Login_LoginsInfo表
        --LoginsID初始值：实例ID+序列号(0001)，新插入记录的LoginsID取Max(LoginsID)+1
        DECLARE @i INT 
        DECLARE @count INT
        SET @i = 1
        SELECT @count = MAX(loginsID)
        FROM   #LoginsInfo
        
        WHILE (@i <= @count)
        BEGIN
            DECLARE @MaxloginsID BIGINT --2014/09/01 15:27 int->bigint
            SELECT @MaxloginsID = ISNULL(MAX(loginsID) ,@InstanceID * 10000) + 1
            FROM   Login_LoginsInfo--设置loginsID
            INSERT INTO Login_LoginsInfo
              (
                loginsID
               ,InstanceID
               ,LoginName
               ,CreateDate
               ,PasswordLastSetTime
               ,IsSystemUser
               ,IsDisabled
               ,IsPolicyChecked
               ,IsExpirationChecked
               ,IsLocked
               ,DaysUntilExpiration
               ,PossibleDisableTime
              )
            SELECT @MaxloginsID
                  ,a.InstanceID
                  ,a.LoginName
                  ,a.CreateDate
                  ,a.PasswordLastSetTime
                  ,a.IsSystemUser
                  ,a.IsDisabled
                  ,a.IsPolicyChecked
                  ,a.IsExpirationChecked
                  ,a.IsLocked
                  ,a.DaysUntilExpiration
                  ,a.PossibleDisableTime
            FROM   #LoginsInfo a
                   LEFT JOIN Login_LoginsInfo b
                        ON  a.InstanceID = b.InstanceID
                        AND a.LoginName = b.LoginName
            WHERE  a.loginsID = @i
                   AND b.InstanceID IS NULL
            
            SET @i = @i + 1
        END
        --Step2 新添加(或首次执行)的登录ID、密码修改时间插入到Login_PasswordSetLog表
        INSERT INTO Login_PasswordSetLog
          (
            LoginsID
           ,PasswordSetTime
          )
        SELECT a.LoginsID
              ,a.PasswordLastSetTime
        FROM   Login_LoginsInfo a
               LEFT JOIN Login_PasswordSetLog b
                    ON  a.LoginsID = b.LoginsID
        WHERE  b.LoginsID IS NULL
        ORDER BY
               a.LoginsID
        --Step3 更新Login_LoginsInfo表中已存在的登录名对应的信息
        UPDATE Login_LoginsInfo
        SET    PasswordLastSetTime = b.PasswordLastSetTime
              ,IsSystemUser = b.IsSystemUser
              ,IsDisabled = b.IsDisabled
              ,IsPolicyChecked = b.IsPolicyChecked
              ,IsExpirationChecked = b.IsExpirationChecked
              ,IsLocked = b.IsLocked
              ,DaysUntilExpiration = b.DaysUntilExpiration
              ,PossibleDisableTime = b.PossibleDisableTime
              ,UpdateTime = GETDATE()
        FROM   Login_LoginsInfo a
              ,#LoginsInfo b
        WHERE  a.InstanceID = b.InstanceID
               AND a.LoginName = b.LoginName
        --Step4 通过登录名ID比对Login_LoginsInfo、Login_PasswordSetLog表中密码最后修改时间，不一致则插入到Login_PasswordSetLog记录表
        INSERT INTO Login_PasswordSetLog
          (
            LoginsID
           ,PasswordSetTime
          )
        SELECT a.LoginsID
              ,a.PasswordLastSetTime
        FROM   Login_LoginsInfo a
               INNER JOIN (
                        SELECT LoginsID
                              ,MAX(PasswordSetTime) PasswordLastSetTime
                        FROM   Login_PasswordSetLog
                        GROUP BY
                               LoginsID
                    ) b
                    ON  a.LoginsID = b.LoginsID
        WHERE  a.PasswordLastSetTime <> b.PasswordLastSetTime
        --Step5 2014/12/25 15:48 已删除的登录名，将Login_LoginsInfo表中字段IsDeleted标记为1
        UPDATE Login_LoginsInfo
        SET    IsDeleted = 1
        FROM   Login_LoginsInfo a
               LEFT JOIN #LoginsInfo b
                    ON  a.InstanceID = b.InstanceID
                        AND a.LoginName = b.LoginName
        WHERE  b.InstanceID IS NULL
    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

使用以下语句获取【实例登录名权限信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_LoginAndRole]    脚本日期: 02/08/2017 14:50:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2015/01/24 14:27
    <中文名称>    获取业务数据库登录名权限信息
    <功能说明>    采集当前SQL实例下面的业务数据库登录名权限信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：请在需要的业务库下创建此过程
    <调用示例>  exec DBA_Pro_Get_LoginAndRole 100233
【修订记录】
-------------------------------------------------------------------
    <2016/09/06 14:27>  <DBA>: 创建 
******************************************************************/
ALTER proc [dbo].[DBA_Pro_Get_LoginAndRole]
    @InstanceID INT
AS
BEGIN
    SET NOCOUNT ON;

    IF (@InstanceID<100000 or @InstanceID>999999)
    begin
        print '数据库实例编号参数错误！'
        return
    end

    BEGIN TRY
    --登录名权限角色信息
    if OBJECT_ID('tempdb..#Temp_Role')is not null
        drop table #Temp_Role

    create table #Temp_Role
    (
        DatabaseName varchar(32) not null,
        DatabaseRole varchar(64) not null,
        RoleMember varchar(64) not null,
        MemberType varchar(64) not null,
        LoginName varchar(64)
    )

    EXEC sp_MSForEachDB
         'insert into #Temp_Role
         SELECT "?" dbname,g.name dbrole,u.name rolemember,u.type_desc,suser_sname(u.sid) loginname
         FROM [?].sys.database_principals u
         INNER JOIN [?].sys.database_role_members m 
         ON u.principal_id = m.member_principal_id
         INNER JOIN [?].sys.database_principals g
         ON g.principal_id = m.role_principal_id
         ORDER BY 1,2'
    /*
    insert into #Temp_Role(DBname,DBrole,LoginName)
    select db_name() [DBname],g.name[DBrole],u.name[LoginName]
    from 
        sys.database_principals g
    inner join
        sys.database_role_members m on g.principal_id = m.role_principal_id
    inner join
        sys.database_principals u    on u.principal_id = m.member_principal_id 
    order by DBname,DBrole*/

    --添加新的登录名权限信息
    insert into DBA_Monitor.dbo.Info_LoginRole(InstanceId,DatabaseName,DatabaseRole,RoleMember,MemberType,LoginName,Updatetime)
    select @InstanceID InstanceId,a.*,getdate() from #Temp_Role a
    left join DBA_Monitor.dbo.Info_LoginRole b with(nolock)
    on a.DatabaseName=b.DatabaseName and a.DatabaseRole=b.DatabaseRole and a.RoleMember=b.RoleMember
    where b.DatabaseName is null

    --更新登录名权限状态    
    update a
    set isdelete=1,Updatetime=GETDATE()
    from DBA_Monitor.dbo.Info_LoginRole a with(nolock)
    left join #Temp_Role b with(nolock) 
    on a.DatabaseName=b.DatabaseName and a.DatabaseRole=b.DatabaseRole and a.RoleMember=b.RoleMember
    where b.DatabaseName is null

    if OBJECT_ID('tempdb..#Temp_Role')is not null
        drop table #Temp_Role
    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码
posted @   Uest  阅读(280)  评论(0)  编辑  收藏  举报
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
点击右上角即可分享
微信分享提示
SQL AI 助手