I was on twitter today a little bit, and I always forget that Tuesdays are T-SQL Tuesdays, and apparently you’re supposed to make a blog post with something T-SQL related. So as is painfully obvious, I’m new to this whole blogging thing. Well sort of. I mean I do have what 5 posts now? ROLLINNN
So today (or I guess this…month?) the focus for blogging is on monitoring. Well I have a little piece of something I can actually share for this. It’s using WMI and WQL coupled with the native alert system to monitor non-production instances’ security (and more if you want!)
Some background on why I’m using this and where it came from
Back in 2012, my organization was working on a consolidation effort. There were too many instances and Microsoft came in for a true-up. Since licensing costs were going up in the future, and we were honestly wasting resources and money by having so many little servers sitting out there, a consolidation project was started in order to have many smaller databases on one instance.
Production was pretty easy
We basically put together a decent list of databases that could go onto the same instance based on version and performance requirements. The permissions were easy too, since those little instances they were on had already been “productionalized” and did not have elevated permissions for users.
But what about non-production?
In prod, we basically ended up taking about 4-6 servers and consolidated them into 1. It made for some nice gains in many areas including licensing, hardware, and even datacenter space. Still, all of these databases had their own non prod boxes…some many different environments. We decided we’d go the route of *also* consolidating non-prod. So what does that mean? Well, it meant that different development teams would be sharing an instance, and that meant nobody got sysadmin or even db_owner rights. It also meant sharing SSIS and SSRS. I’m not going to go into what I said in those last 2 sentences because I cover it more in depth in my “Know Your Role” presentation that I give at user groups and SQL Saturdays and I’ve probably already lost some people’s attention.
The problem I looked at though, is how do we KEEP people out of those 2 elevated roles?
Anybody got a junior dba on their team?
Anybody also not trust them as far as you can spit?
Or maybe just have folks on your team that aren’t aware of the work you put in to keep people from elevated rights?
Naturally most people feel like if they see a development environment, and someone asks for elevated rights to it, it isn’t a big deal. On these consolidated servers though, it was a very big deal. What if one team ran out of space, had sysadmin rights and decided to drop a database for a different development team? You can see where this would be problematic, although I can give you plenty of other examples if you don’t.
As a side note, this is also an issue you can run into if you’re hosting database as a service at your organization and are expected to manage it. Same concept.
Well, get on with it then
So here are my WMI alerts. Please note you’ll have to change “MSSQLSERVER” to your instance name if you have a named instance, and I’d also change “.” to the actual servername.
EXEC msdb.dbo.sp_add_alert @name=N'Database Role Membership Change',
@notification_message=N'Role $(ESCAPE_NONE(WMI(RoleName))) was altered for Login $(ESCAPE_NONE(WMI(TargetUserName))) on database $(ESCAPE_NONE(WMI(DatabaseName))) on server $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))',
@wmi_query=N'SELECT * FROM AUDIT_ADD_MEMBER_TO_DB_ROLE_EVENT',
EXEC msdb.dbo.sp_add_alert @name=N'Server Role Membership Change',
@notification_message=N'Role $(ESCAPE_NONE(WMI(RoleName))) was altered for Login $(ESCAPE_NONE(WMI(TargetLoginName))) on server $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))',
@wmi_query=N'SELECT * FROM AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT',
EXEC msdb.dbo.sp_add_notification @alert_name=N'Database Role Membership Change', @operator_name=N'DB_Admin', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Server Role Membership Change', @operator_name=N'DB_Admin', @notification_method = 1
Ok, what’s all that do?
These 2 alerts will send email notifications to the operator “DB_Admin” ANYTIME there is a database or server level role change. It will not capture explicit permissions; however, such as CONTROL at the server or database level. As with any query you find on the internet, I take no responsibility for it. If you run it without reviewing it; it’s all on you.
Can’t you do that with Policy Based Management?
Yes you can. For whatever reason, my google skills failed me when looking up this option back in the day, and I wasn’t able to find anything on it. Since this is on dev, pretty regularly a vendor upgrade will require elevated rights for a service account; and instead of adding an exception to PBM temporarily, I’d rather just get an email about it and ask. Plus the above alert will tell you exactly who has granted those permissions and will do it for every role, not just sysadmin (securityadmin is still a loophole if you only monitor for sysadmin). Still, if you’d rather use PBM here’s a nice article on how to set that up:
Here’s some additional links for you: