SQLServerCentral Runs sp_Blitz - Security

Question

Post reply

SQLServerCentral Runs sp_Blitz - Security

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Brent Ozar SSCrazy Points: 2451 More actions · Answer 1

The question is - how do you get a list of named instances inside T-SQL? I'm not aware of a way to do that.

quackhandle1975 SSChampion Points: 11055 More actions · Answer 2

I guess I was thinking a bit too far out of the box! :crazy:

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Brent Ozar SSCrazy Points: 2451 More actions · Answer 3

Hahaha, yeah. There's a few similar things I'd like to be able to detect (partition alignment, driver versions, etc) but there's only so far I can go in T-SQL. I've heard so many people saying they'd like to build a PowerShell equivalent to do similar health assessments, and I'm really surprised I haven't seen one by now.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Could you use xp_regread or something similar?

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Brent Ozar SSCrazy Points: 2451 More actions · Answer 5

I'd love to see someone else do it, but I'm not aware of how to enumerate the list of instances that way.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

This worked on all of the instances I tried it on.

declare @value varchar(max)

EXEC master..xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

@value_name = 'InstalledInstances',

@value = @value OUTPUT

It returned a list of all installed instances on the servers I tested it on. That only includes 2000-2008 R2 and limited OSs. You would probably have more options to test than I would.

I'm not sure how useful this will be however since I also read that extended stored procedures have been listed as depreciated in 2012.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Brent Ozar SSCrazy Points: 2451 More actions · Answer 7

Ooo, nifty! Thanks, I'll give that a shot - I don't have a multi-instance VM at the moment, but I happen to be working with a client later today that's got a bunch, and I'll give it a shot there and let you know how it goes.

quackhandle1975 SSChampion Points: 11055 More actions · Answer 8

Sorry Brent, at the risk of this becoming an sp_Blitz steering group ;-), I was wondering if a future version of blitz could pick up if a SQL instance (in a maint plan or t-sql) has the Backup set to expire = 1 day? Just for informational purposes?

Thanks

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Brent Ozar SSCrazy Points: 2451 More actions · Answer 9

Ha! No worries. Do you have T-SQL handy to detect that? If so, can you email it to me at help@brentozar.com? If so I'll add it to the check, but I don't know offhand how to grab that, and I've stopped adding checks that I have to research myself. (Just already have a laundry list of stuff I want to add.)