SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / SQLServerCentral Runs sp_Blitz - Security / Latest Posts

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Fri, 22 Feb 2013 06:01:44 GMT

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.)

RE: SQLServerCentral Runs sp_Blitz - Security

quackhandle1975 — Fri, 22 Feb 2013 04:39:38 GMT

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[b][i]qh[/i][/b]

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Tue, 19 Feb 2013 05:33:29 GMT

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.

RE: SQLServerCentral Runs sp_Blitz - Security

Kenneth.Fisher — Mon, 18 Feb 2013 21:47:48 GMT

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 OUTPUTIt 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.

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Mon, 18 Feb 2013 12:54:04 GMT

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

RE: SQLServerCentral Runs sp_Blitz - Security

Kenneth.Fisher — Mon, 18 Feb 2013 12:52:34 GMT

Could you use xp_regread or something similar?

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Mon, 18 Feb 2013 06:03:24 GMT

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.

RE: SQLServerCentral Runs sp_Blitz - Security

quackhandle1975 — Mon, 18 Feb 2013 06:00:20 GMT

I guess I was thinking a bit too far out of the box! :crazy:[i][b]qh[/b][/i]

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Mon, 18 Feb 2013 05:49:26 GMT

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.

RE: SQLServerCentral Runs sp_Blitz - Security

quackhandle1975 — Mon, 18 Feb 2013 05:47:51 GMT

No problem, glad I can help improve sp_Blitz in some way. Could you simply get a list of named instances and do a count, say more than 2 and it gets flagged up? Granted as a prod DBA, my dev skills aren't as strong as some. :unsure:[i][b]qh[/b][/i]

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Mon, 18 Feb 2013 05:39:43 GMT

[quote][b]quackhandle1975 (2/18/2013)[/b][hr]Just a question for a possible upgrade, have a Server here with 8 sql instances installed (yes on a prod server, I know, I know!) , maybe a future version of blitz could pick this up?[/quote]HAHAHA, wow, I bet that was a fun surprise. 8 instances! I've never seen that many outside of a cluster (and of course all of the instances weren't on the same node unless there had been a complete disaster).I loooove this idea. Now I gotta figure out how to detect it from inside T-SQL. I can't use sys.dm_server_services because that only returns the current instance's related services. I did some quick Googling and there's an interesting solution involving xp_cmdshell, but that's not usually enabled:http://stackoverflow.com/questions/714413/how-to-detect-all-sql-server-instances-from-studioI've heard from a lot of people who've worked on their own PowerShell equivalent of sp_Blitz™, and it'd be easier to detect there. Dang!

RE: SQLServerCentral Runs sp_Blitz - Security

quackhandle1975 — Mon, 18 Feb 2013 05:29:20 GMT

Another +1 for sp_Blitz from me. I am currently providing some SQL consultancy to an SME with < 20 SQL instances and sp_Blitz has been perfect for me to diagnose quickly and effectively as to what SQL is doing (or is not doing!) and how it was installed, tweaked etc. Obviously I need to check other aspects of each instance, but it's an excellent tool for any prod DBA.Just a question for a possible upgrade, have a Server here with 8 sql instances installed (yes on a prod server, I know, I know!) , maybe a future version of blitz could pick this up?Thanks Brent! :satisfied:[i][b]qh[/b][/i]

RE: SQLServerCentral Runs sp_Blitz - Security

Kenneth.Fisher — Thu, 10 Jan 2013 15:39:58 GMT

Excellent tool and one I've been looking at myself. Just to add to the comments about using SA as the database owner. There is a somewhat obscure potential security hole that can be exploited. It's actually one I've used myself to bypass some permissions issues. If your database is set to trustworthy and is owned by any sysadmin then you are effectively giving a free pass to anyone with IMPERSONATE on dbo in that database. You can easily create a stored procedure in the dbo schema with the EXECUTE AS OWNER clause that can perform any task that a sysadmin on the server can. Now, not a common situation, but if you don't know it's coming it would be very easy to create an unintended security hole.Just as a note you can set the owner of the database to a login with just CONNECT permissions. I've never done it mind you except in testing, so I don't know what implications it might have.

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Thu, 10 Jan 2013 12:59:16 GMT

[quote][b]GSquared (1/10/2013)[/b][hr]Can I call this proc recursive? Under the headings of Performance, it shows some problems coming up from proc sp_Blitz. :-)[master].[dbo].[sp_Blitz] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.[/quote]Hahaha, yeah, we've got that fixed in v17 coming out this month. I facepalmed big time when I saw that.

RE: SQLServerCentral Runs sp_Blitz - Security

GSquared — Thu, 10 Jan 2013 12:53:52 GMT

Can I call this proc recursive? Under the headings of Performance, it shows some problems coming up from proc sp_Blitz. :-)[quote][master].[dbo].[sp_Blitz] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.[/quote]It also comes up for the Query Plans section of its own findings. Compares some columns that aren't the same datatype.On the other hand, it correctly informs me that my database ProofOfConcept2000 is running in Compat 80. And that my ProofOfConcept2008R2 database has a huge number of single-use plans in the cache. Both are known things (by-design in both cases), but it's good to see the tool catching that kind of thing.I just ran it on a proof-of-concept server (SQL 2008 R2 running on a desktop workstation), using the default settings. Told me exactly what I'd expect from that machine. That's a good thing! No false-positives, nothing trivial that I'd dismiss out of hand (good signal:noise).Edit: The "comparing two fields that aren't the same datatype" messages are actually coming from database ReportServer. Per sp_Blitz, that database and ReportServerTempDB violate all kinds of best practices. Of course, that's a known thing. Microsoft always follows, "do as I say, not as I do" in that regard.

RE: SQLServerCentral Runs sp_Blitz - Security

Brent Ozar — Thu, 10 Jan 2013 12:08:06 GMT

[quote][b]PAH-440118 (1/10/2013)[/b][hr]One point: You refer to making SA the database owner. I agree this should be set to a suitable account but I was always of the opinion that the SA account should be removed or at least disabled.[/quote]Yep, you can disable the account and still have SA be the owner. Also, be aware that renaming SA can have side effects - http://support.microsoft.com/kb/968829 is a good example, which broke SQL 2008 upgrades if SA was renamed. I've seen lots of other apps/products that demanded the literal login SA. (Hey, I know it's a bad practice, I'm just sayin' they're out there.)Another side note - even if you disable logins for SA, other accounts can still impersonate the SA account. Disabling login doesn't disable impersonation.

RE: SQLServerCentral Runs sp_Blitz - Security

Steve Jones - SSC Editor — Thu, 10 Jan 2013 10:08:50 GMT

[quote][b]PAH-440118 (1/10/2013)[/b][hr]Thanks for bringing this tool to my attention. I will have a go with it to see if it can help us to get our house in order. One point: You refer to making SA the database owner. I agree this should be set to a suitable account but I was always of the opinion that the SA account should be removed or at least disabled. Another account with the same privileges should be created to use instead.By using SA you are giving potential hackers 50% of the 2 part login (user name / password). If you use SA they only need to find the password, whereas if SA is removed or disabled they need to find the account name and the password. - I would appreciate views from the community :discuss:[/quote]I haven't seen any issues here. The truly technical hacking discussions I've seen show ways to discover who is a sysadmin, and if it's any SQL login, the attacks are similar. It's not something I've worried about, and you can still set owners to SA, even if you run Windows Auth only.

RE: SQLServerCentral Runs sp_Blitz - Security

Richard M. — Thu, 10 Jan 2013 08:38:48 GMT

[quote][b]jeffreddy (1/10/2013)[/b][hr][quote][b]SQLDBA360 (1/10/2013)[/b][hr]*prepairing to be shot downI've never had any problems setting SA as the DB owner with the SA account disabled, its a practice I follow even when the instance is in Windows Authentication only[/quote]Before you criticize anything in this article, go to http://www.brentozar.com/blitz/, download the code for the sp_Blitz stored proc and run the stored procedure. The stored procedure will then return results about your server, including any SA/DB Owner issues. But it also includes URL links to articles explaining why these issues are bringing brought to your attention. There are times when many of the things outlined are acceptable. The authors (Brent Ozar & Team), aren't preaching a set of must dos and don'ts, but rather guidelines. These guidelines often have exceptions to the rule.I would suggest looking into the meat of sp_Blitz a little deeper before bashing the author of this 'Intro to sp_Blitz' article. I'm guessing here that Brent Ozar and his team are a bit more knowledgeable than you regarding SQL Server, so it's probably good advice to listen to them. At least look into their reasoning before bashing the author here. [/quote] .... as your first post in the forum you took a rather aggressive approach to "comment" on a very valid post sharing a personal experience.... maybe you should keep lingering and reading...for now. We use sp_blitz frequently and have done so for quite a few iterations of the script.... a must have tool for any DBA.

RE: SQLServerCentral Runs sp_Blitz - Security

SQLDBA360 — Thu, 10 Jan 2013 08:09:44 GMT

At what point am I criticizing sp_blitz? i'm trying to share my experience with using SA as the database owner as per the comment above. I agree with you glowing endorsement of Brentozar.com, I own a copies of the books by both Brent and Jes and have attended numerous free and paid training events.

RE: SQLServerCentral Runs sp_Blitz - Security

jeffreddy — Thu, 10 Jan 2013 06:55:37 GMT

Great Article. Rated max stars. Visit http://www.brentozar.com/

RE: SQLServerCentral Runs sp_Blitz - Security

SQLDBA360 — Thu, 10 Jan 2013 03:32:33 GMT

*prepairing to be shot downI've never had any problems setting SA as the DB owner with the SA account disabled, its a practice I follow even when the instance is in Windows Authentication only

RE: SQLServerCentral Runs sp_Blitz - Security

PAH-440118 — Thu, 10 Jan 2013 03:25:28 GMT

Thanks for bringing this tool to my attention. I will have a go with it to see if it can help us to get our house in order. One point: You refer to making SA the database owner. I agree this should be set to a suitable account but I was always of the opinion that the SA account should be removed or at least disabled. Another account with the same privileges should be created to use instead.By using SA you are giving potential hackers 50% of the 2 part login (user name / password). If you use SA they only need to find the password, whereas if SA is removed or disabled they need to find the account name and the password. - I would appreciate views from the community :discuss:

RE: SQLServerCentral Runs sp_Blitz - Security

SQLDBA360 — Thu, 10 Jan 2013 03:13:40 GMT

Great introduction to this valuable tool

SQLServerCentral Runs sp_Blitz - Security

Steve Jones - SSC Editor — Wed, 09 Jan 2013 22:19:08 GMT

Comments posted to this topic are about the item [B]SQLServerCentral Runs sp_Blitz - Security[/B]