SQL读取注册表值

最近写一个自动检查SQL Serve安全配置的检查脚本,需要查询注册表,下面是使用SQL查询注册表值的方法.

 

 1 DECLARE @HkeyLocal nvarchar(18)
 2 DECLARE @Instance varchar(100)
 3 DECLARE @MSSqlServerRegPath nvarchar(200)
 4 DECLARE @PortNumber varchar(100)
 5 
 6 --For Named instance 
 7 --SET @Instance ='MSSQL13.SQL01'
 8 SET @Instance ='MSSQL13.MSSQLSERVER'
 9 SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
10 SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'+ @Instance + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
11  
12 Print @MSSqlServerRegPath
13 EXEC xp_instance_regread @HkeyLocal
14 , @MSSqlServerRegPath
15 , N'TcpPort'
16 , @PortNumber OUTPUT
17 SELECT @PortNumber as [Port Number]
18 --OUTPUT
19 
20 
21 
22     • 读取instanceHide在注册表中的值
23 DECLARE @Instance varchar(100)
24 set @Instance='MSSQLServer'
25 DECLARE @MSSqlServerRegPath nvarchar(200)
26 set @MSSqlServerRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\'+@Instance+ N'\SuperSocketNetLib'
27 print @MSSqlServerRegPath
28 DECLARE @getValue INT
29 EXEC master..xp_instance_regread
30 @rootkey = N'HKEY_LOCAL_MACHINE',
31 @key=@MSSqlServerRegPath,
32 @value_name = N'HideInstance',
33 @value = @getValue OUTPUT
34 SELECT @getValue as HideInstance
35 
36 
37 ----测试对命名实例读取instanceHide
38 DECLARE @HkeyLocal nvarchar(18)
39 DECLARE @Instance varchar(100)
40 DECLARE @MSSqlServerRegPath nvarchar(200)
41 DECLARE @PortNumber int
42 
43 --For Named instance 
44 --SET @Instance ='MSSQL13.SQL01'
45 SET @Instance ='MSSQL13.MSSQLSERVER'
46 SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
47 SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'+ @Instance + '\MSSQLServer\SuperSocketNetLib'
48  
49 Print @MSSqlServerRegPath
50 EXEC xp_instance_regread @HkeyLocal
51 , @MSSqlServerRegPath
52 , N'HideInstance'
53 , @PortNumber OUTPUT
54 SELECT @PortNumber as [Port Number]
55 --OUTPUT

 

转载于:https://www.cnblogs.com/kala/p/9204396.html