Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQLServerCentral Runs sp_Blitz - Security Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 10:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:53 PM
Points: 33,063, Visits: 15,179
Comments posted to this topic are about the item SQLServerCentral Runs sp_Blitz - Security






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1405170
Posted Thursday, January 10, 2013 3:13 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:19 AM
Points: 728, Visits: 771
Great introduction to this valuable tool
Post #1405302
Posted Thursday, January 10, 2013 3:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:13 AM
Points: 68, Visits: 282
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
Post #1405307
Posted Thursday, January 10, 2013 3:32 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:19 AM
Points: 728, Visits: 771
*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
Post #1405312
Posted Thursday, January 10, 2013 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:32 AM
Points: 2, Visits: 58
Great Article. Rated max stars. Visit http://www.brentozar.com/

Post #1405405
Posted Thursday, January 10, 2013 8:09 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:19 AM
Points: 728, Visits: 771
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.
Post #1405478
Posted Thursday, January 10, 2013 8:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:40 AM
Points: 1,093, Visits: 2,615
jeffreddy (1/10/2013)
SQLDBA360 (1/10/2013)
*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



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.




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #1405502
Posted Thursday, January 10, 2013 10:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:53 PM
Points: 33,063, Visits: 15,179
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


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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1405540
Posted Thursday, January 10, 2013 12:08 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:29 PM
Points: 68, Visits: 414
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.



Post #1405591
Posted Thursday, January 10, 2013 12:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1405611
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse