January 9, 2013 at 10:19 pm
Comments posted to this topic are about the item SQLServerCentral Runs sp_Blitz - Security
January 10, 2013 at 3:13 am
Great introduction to this valuable tool
January 10, 2013 at 3:25 am
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:
January 10, 2013 at 3:32 am
*prepairing to be shot down
I'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
January 10, 2013 at 6:55 am
Great Article. Rated max stars. Visit http://www.brentozar.com/
January 10, 2013 at 8:09 am
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.
January 10, 2013 at 8:38 am
jeffreddy (1/10/2013)
SQLDBA360 (1/10/2013)
*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
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.
.... 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.
January 10, 2013 at 10:08 am
PAH-440118 (1/10/2013)
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:
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.
January 10, 2013 at 12:08 pm
PAH-440118 (1/10/2013)
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.
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.
January 10, 2013 at 12:53 pm
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.
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 10, 2013 at 12:59 pm
GSquared (1/10/2013)
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.
Hahaha, yeah, we've got that fixed in v17 coming out this month. I facepalmed big time when I saw that.
January 10, 2013 at 3:39 pm
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.
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]
February 18, 2013 at 5:29 am
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:
qh
February 18, 2013 at 5:39 am
quackhandle1975 (2/18/2013)
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?
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-studio
I'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!
February 18, 2013 at 5:47 am
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:
qh
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply