监控-系统实例
原始脚本来自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
中间添加的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
原语句取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
使用以下语句获取【实例登录名权限信息】
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
|
|
【作者】： 醒嘞 |
| 【出处】： http://www.cnblogs.com/Uest/ | |
| 【声明】： 本文内容仅代表个人观点。如需转载请保留此段声明，且在文章页面明显位置给出原文链接！ |
