DBAs and managers that must protect sensitive business data.
Do you work for an institution that fears its DBAs could go rogue on their production databases?
Do you want to revoke continuous sysadmin permissions from your DBA team in an attempt to avoid being the next WikiLeak?
Do you have concerns that the DBA team is querying sensitive databases?
Then you need Uptight Database Security. Come to this session to learn how to deploy a configurable permission model that provides on-demand access to SQL Server databases. This model allows DBAs to do routine work without sysadmin, and elevate permissions to sysadmin only during production emergencies or disaster recovery scenarios.
Attendees will leave this session with the fundamental knowledge and scripts to implement a low hassle solution that provides DBAs the minimum necessary access required to maintain a production database server.
Enjoy the Podcast?
Why I Want to Present This Session:
Given all the cyber-security fiascoes in the news, this is a very relevant topic. I developed this model to satisfy the requirements for my previous employer and I’m still using the principles today to control access for our offshore development team.
Brent Ozar: The next session at GroupBy, Ron Dameron is going to be talking about uptight database security. So take it away, Ron…
Ronald Dameron: Alright, thank you, and like I said, thank you Brent and GroupBy voters for giving me a chance to do this. This is like a little screenshot of me doing a happy dance during a softball game. Now, can I switch and show the video real quick?
Brent Ozar: Yeah.
Erik Darling: It’s a good time, man. Go ahead…
Ronald Dameron: Okay, so this was at – can you guys see the video?
Brent Ozar: Yeah, it hasn’t started playing yet, but yeah.
Ronald Dameron: Okay, so I’ll start playing it. So I’m at first base – this only takes about 30 seconds.
Brent Ozar: It looks like a surveillance video.
Ronald Dameron: yeah, it’s up on the backstop. It’s a double play.
Brent Ozar: Nice. And your happy dance…
Ronald Dameron: That’s my happy dance. So I said I would do the happy dance if I got selected for PASS summit. I did the happy dance when I got selected for this because I was like – I initially was like, “I’m not going to do this anymore because nobody ever comes to see it. I get like five people, ten people, at a SQL Saturday…
Brent Ozar: Well you got 200 people in here.
Ronald Dameron: Oh my god, really?
Brent Ozar: Yeah.
Ronald Dameron: Okay, and the last time I did this – I just did this talk at SQL Saturday in Orlando, and the room was packed. I was like wow. So I was pretty happy about that.
So just a little bit about me, I’ve a distant cousin in the Rebel Alliance; his name’s Poe Dameron. I’m the only DBA for a small software company now. I used to work – when I did this project, I worked for a big insurance company. And now, my job is split between dealing with [lots of] stuff – we have two SQL Servers on premises, and it’s in an availability group, so I get to play around with that, fix it when somebody breaks it – which Hurricane Irma actually broke it, so that was a fun experience. And the reason I’m called – I’ve worked for – and this is where SQL Saturday helped me find my next job and why I could do my job interview in a polo and khakis is I met a guy called Dimitri Korotkevitch, who’s another MCM, at a SQL Saturday like five years ago. My last company decided they wanted everybody to move to North Carolina, and I’m like, “I live in Florida. I’m not moving to North Carolina.” Raleigh, North Carolina – nothing against Raleigh, but compared to – I get to play softball all year round here in Florida. Plus my wife would have said, “No way, I’m not going.”
So we were at a SQL Saturday – the last Tampa SQL Saturday, which was a couple of years ago, because last year they couldn’t do it, they were doing something in USF with the building. We were talking to – me and Dimitri, who was at the time director of application development, we were talking to another MVP from down south Florida, Herve Roggero. He’s now got a startup called Enzo. And he asked me, “What are you doing these days?” I said, “Well I work for Dimitri, I’m a lead DBA.” And Dimitri goes, “You’re the only DBA.” And I’m like, “That’s my job title,”: because we kept going back and forth during the job interview, and I was like, “What’s my job title going to be, because I have to support on premises stuff, but then I also have to support Azure stuff;” Azure SQL database and monitoring and alerting. So for now on my signature block in my email I have, “The only DBA.” Anyway, enough about me…
Erik Darling: Your current job title matters less than the job title you put on your resume when you’re looking for the next one, right?
Ronald Dameron: I know. I’m trying to figure out – because now they’ve added me to a dev ops team, I actually have to do a development task – I’m trying to figure out – okay, I’ve set up a bunch of alerts but I’ve used a portal to do it. I’m like, there’s got to be an easier way to do this. but the PowerShell – PowerShell in Azure is still a pain in the butt.
Erik Darling: You know what’s awesome about dev ops; is that like hardly anyone has like the dev ops handle, so you could totally be dev ops Ron. Like SQL Ron or Ron SQL is long gone, man. All those SQL handles are just out of the door. You can be dev ops all day.
Ronald Dameron: I thought about changing it somehow, but then I fear that kind of screws up all your Twitter timeline.
Anyway, here’s the agenda today. I’m going to talk about why I had to do this, and I’m going to talk about, do you always have to be sysadmin to do your DBA job, your daily tasks; what I would say are your routine tasks? Then we’ll talk about the six steps you need to go through, plus some assumptions, to lockdown your databases. And then I’ll do a really quick demo – it’s just one big script with those six steps in it. I’ll talk about the resources that I used to come up with this solution, and then take questions. And I’ll take questions throughout the time, Erik. I expect it will go about an hour. It normally doesn’t go much longer than that, unless people start asking great questions.
Erik Darling: I have cleared out my schedule.
Ronald Dameron: Okay, anyway I’m going to try and put a poll into Slack…
Erik Darling: If you want me to type it, I can do it for you?
Ronald Dameron: I got it – let me see here – where is… Og, it’s over here. Let’s see if this works. Well, thanks to Brent for giving me the syntax on that. I saw you guys doing it last week and I’m like, “Okay, good, DBAs…”
Erik Darling: yeah, it’s a lot of fun.
Ronald Dameron: I learned during Speaker Idol – I did Speaker Idol a couple of years ago and they said, “Find out who’s in the audience to start.” [crosstalk].
Erik Darling: Makes sense – find out who you’re talking to. Wow, two security people. I never would have guessed.
Ronald Dameron: Well, and this was kind of some of the people who were kind of instigating the whole thing. Okay, so let me get back to my screen. Okay, good, thank you very much.
So yes, believe it or not, DBAs, you can do your job and you don’t have to be sysadmin all the time. Now, I never want to be this DBA. This is a good reason why you would want to do this, especially if you’re in a really big company. This happened at Anthem Insurance, which is a Blue Cross Blue Shield provider, a couple of years ago. One of their database administrators saw queries running on their production servers that they didn’t kick off.
Erik Darling: Uh-oh…
Ronald Dameron: Yeah, and they often found other database administrator’s logins had been compromised. So this is – if this doesn’t send a chill down your spine, I don’t think anything will. Like I said, when I was initially tasked with this project, I pushed back because I thought it would be a royal pain to do being a DBA, because at the time I was supporting a line of business, but I was also – I think I had been supporting a line of business and I’d gotten promoted into another team that was more like an engineering team. Again, big company, and this was in 2006 or 2007, and again, insurance company. So at that point, we still have a lot of SQL Server 2000 running. And I told the VP that was instigating this, who had come from a mainframe background. He goes, “We can do this on the mainframe, can we do this in SQL Server?” I said, “Not right now. I’d have to do some research.” But I mean, if you look at SQL Server 2000 and you look at some of the stored procedures, the system stored procedures, several of them have checks that say, and it checks your login, “Are you sysadmin? Okay, fine, do what you want.”
I mean there’s no permission check. If you’re sysadmin then the door is wide open for you. Now, this got easier when 2005 came out. They added that control server permission, then things started looking like this might be possible to do. Then, of course, like I said it being an insurance company, it took another year or two for them to finally start agitating for this more.
So, why would you want to do this? Obviously, to decrease risk, auditors that were looking at our environment, they don’t like always on sysadmin, so that’s kind of an homage to Alan Hurt. They wanted more control of the production databases, because at the time we had 600 SQL Servers, at the time, and we had like 50DBAs, both onshore and offshore. And everybody had sysadmin rights. They wanted it to be changed to where you only needed sysadmin when you needed it. And then the other reason you would do this is it’s just another defensive layer. If someone gets into your network, if you’re a DBA and don’t have privileged access, you don’t have sysadmin rights, then it’s going to be harder for them to do anything harmful.
So, they called it IT Security Audit and Compliance, at the previous company, and they were the ones that were agitating the most about it. So now, the requirements at the company were sysadmin access only when needed, and that DBAs don’t have access to user data. And like I said, that VP who had come from the mainframe world, at that time when we were talking about this – I guess we started talking about it again in 2007, maybe even later, 2009 – because they sent me to PASS Summit in 2010, and that’s when I saw Lara Rubbelke and [inaudible] Son Lee do a presentation on the first Microsoft Separation and Duties whitepaper. And what’s funny is that in that whitepaper – there’s a quote in that paper right at the beginning that says, “Do not use the sysadmin role as a general purpose DBA role.” And I’m like, “That’s what everybody does.”
I mean, I think that from what they were telling – and I talked to Lara Rubbelke after the talk and she said the reason they had to start doing this is because they were getting, I guess, requests from customers saying we need to be able to control DBA’s access to production data. And that’s why this came about.
So, it took a lot of convincing – I started interviewing the DBA teams. Like I said, I’d got hired initially as a DBA to support a certain line of business, so I knew what tasks I did. And I started documenting them in a spreadsheet, trying to figure out what do we do every day and what do other DBAs do? And some of those interviews were like pulling teeth because the offshore team would – like I’d say, “Okay, what do you do?” And dead silence. So it got to the point where, “Okay, do you do this? Do you do this? Do you do this?” So I had this big spreadsheet with all these tasks, and then I spent days researching what permissions you need to do these tasks. So when I initially started doing this talk a number of years ago, I would have this quiz at the beginning. So to run sp_configure, do you need to be sysadmin? Do you need it for when you run it with zero parameters, one parameter, two parameters? Do you need it when you run reconfigure?
And, believe it or not, the answer is you don’t need sysadmin rights to run this particular command. If you run it with no parameters or only – let’s see, the first parameter, any user can do that. Now, if you execute sp_configure with both parameters and you want to change a configuration option or run the reconfigure statement, you have to have the alter settings server level permission. Now, that’s implicitly granted to sysadmin or server admin fixed server roles. Again, we were trying to prevent people from having sysadmin rights all the time.
And then the reconfigure permissions, that defaults to people who have alter setting permissions. And sysadmin and server admin have that also. Now, do you need t0o be sysadmin to kill a process? To view the SQL Server error log? Or to run DBCC CHECKDB? The killer process, again, you don’t have to be sysadmin. I had to make notes for this one because it’s different by version. 2012…
Erik Darling: Which always makes security so much easier…
Ronald Dameron: Oh, well, and that’s the thing with this. I work in a small company now; this is overkill. So if you’re in a small company and there’s a small team – I mean, you have to figure out what do you do, you have to figure out is this worth your time, and there’s a few other things that I’m going to talk about that cost a fair amount of money, I’m sure, to enable this. But for killing a process, you only need alter any connection permission. Again, that’s included whether you’re sysadmin or process admin. Now, SQL Server 2005, again, you would either have to be sysadmin or process admin. Now, the error log – viewing the SQL Server error log, that one’s actually kind of a pain in the butt because trying to figure out what the exact permissions were – there’s a stored procedure that gets called sp_readerrorlog and then xp_readerrorlog; and I have a link to a log file here, the log file viewer topic. And it ends up just – we had enough permissions to – they couldn’t see it through management studio, but they could see it in the file system. But I’ll tell you how we handled that later on.
And then DBCC CHECKDB, you either have to be sysadmin or you have to be DB owner in the database; so more proof that you don’t always have to be sysadmin to do routine stuff. Now, the last thing I’ll talk about as far as the permission quiz is do you need permissions to – I mean, do you need sysadmin rights to select on DMVs? And the answer is no. You just need VIEW SERVER STATE or VIEW DATABASE STATE, depending on what you are interested in looking at, and then what positions do you need to update DMVs? I hope to god that nobody answers that question. That’s a trick question. I’m just making sure you’re paying attention.
So, the assumptions – you have an audit tool at your workplace. At my particular company, at the time, they were using IBM’s Guardium, that they would install on each SQL Server to monitor the traffic. And they also had a vulnerability assessment tool. What I mean by patching done regularly, obviously your SQL Servers are getting patched, but then also your network admins are doing everything that they need to do to secure the server. Because what I’m talking about, it’s just securing access to the database. You’re going to need a password vault. We used a product called CyberArk. And then you’re also going to have to have separation of duties, and what that was is our DBAs at the insurance company were not allowed to create logins or drop logins. That had to o through – there was another department and a website that you would submit a task and say, “Hey, I need login access to SQL Server, Oracle, Db2.” And DBAs, the only thing we did was set permissions.
Then again, all of that stuff was audited with Guardium tools. And that was something that periodically I would get back from the IT risk people, like, “Hey, can you look at this query? It came from an unknown IP address.” And it was like selects on system tables and a few times it was actually me who had run them. And I’m like, “Okay, that’s me and it’s harmless.” I just kind of laughed and said, “That’s me running those queries. You’re sending it to the guy who executed the queries.”
Erik Darling: We’ll go right to the source then, that’s perfect.
Ronald Dameron: There you go. And I’ll say it again; you have a security audit tool.
Now here’s an example. I added this because, like I said, I work for a small company now, there’s only – instead of 5000 people in IT, or there was like 50,000 people in the last company…
Erik Darling: A couple of questions came in. I don’t mean to throw you off; any recommendations on an auditing tool, aside from the one that you mentioned?
Ronald Dameron: Oh, Idera has a good tool that secures – I can’t remember the name…
Erik Darling: I’ll track it down.
Ronald Dameron: Yeah, Idera has a product that one of the companies that we acquired had. And that – and I even advocated for that at some point because I was like – we were getting questions form auditors and I said, “We could answer these questions really fast with this Idera tool.” So Idera has a tool and some kind of security, SQL Security something… I forget the exact title, but that would be something I would check out because that makes it simple. But then, the only issue with that is I think it’s only SQL Server. And for us, at that big company, we had, like I said, Db2 on mainframe, Db2 on Unix, Oracle, SQL Server. So we were always looking for something cross-platform. I mean, BMC was a big vendor at that company. Any other questions?
Erik Darling: there’s one, but I need verification on it, so go ahead…
Ronald Dameron: Okay, so I work for a small software company now, and again, I’m the only DBA onshore. The other guy offshore does more development work, but also does DBA tasks. And when my director of application development has moved on to another job, so he asked us to grant this offshore DBA some additional permissions so he could do outage troubleshooting – in case I wasn’t around – and also…
Erik Darling: Cause some outages…
Ronald Dameron: Yeah right, it’s like – I’m on pager duty from 9 am to 9 pm, and then the offshore guys take it from 9 pm to 9 am.
Anyway, the job responsibilities were outage troubleshooting and also to get him familiar with the production environment, because before, he didn’t have access to it, because Dimitri and I handled the production side onshore. He had read-only access. So we gave him DbDataReader to two databases, [inaudible], CONNECT SQL, VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW SERVER STATE. And he can see what he needs to see in case there’s issues. But then, like I said, that’s a small – he doesn’t have sysadmin rights. So if you’re in a small company, it is possible to do things. As long as you scope out what the tasks are and do some research, more often than not, you can set this up and not grant the whole kit and caboodle to somebody.
Erik Darling: So the question that came in was, “Where I work, I have a security team that creates the Windows logins, but if an application uses SQL authentication then the DBA has to create the login.” So the duties are somewhat separated, but what would be the best practice there?
Ronald Dameron: And I think that the way we set it up, initially they had set up some – I don’t know what they use now, because it’s been two and a half years since I was working there, but they bought a tool that they could submit the request to. I had to write some PowerShell scripts, they would produce a file and I would have to read the file and grant the permissions. At the time, I was using – this was before dbatools people were doing their stuff – a guy named Chad Miller, who I used to work for, he created a set of functions, SQLPSX. And I’ve used those functions to do all the permissions and granting logins. But I think we were able to do both – we did just the SQL Server login provisioning, and it didn’t matter – we would ask, do you want Windows authentication or SQL authentication. But then beyond just the login provisioning, we did automate it to the point where we set the permissions. Other than – we could say, “Okay, the person needs DbDataReader on a certain database,” but if it got any more granular than that, we had to get the DBA involved. But you can automate the provisioning of SQL authenticated logins. We did it. And I think it’s got to be easier now with the dbatools functions that Chrissy LaMaire and Drew and all those guys, and SQL DBA with a Beard are working on – Rob Sewell…
Erik Darling: I’ll stick a link to there in the…
Ronald Dameron: I used their – I had to migrate – I had one dev server that was getting retired and I had to migrate stuff to a virtual. It was a physical to virtual, and I just used their database migration tools. I just like type in command name, database, you know, from, to, server name, and just watched it go. So it was – those were some awesome tools.
So okay, to lockdown a database, these are the steps. You’re going to need sp_foreachdb. That’s from Aaron Bertrand…
Erik Darling: Ooh, that’s over in the First Responder Kit.
Ronald Dameron: It’s also – actually, funny story about that. Brent, I guess, contacted Aaron sand said, “Hey, I want to put sp_foreachdb in the First Responder Kit.” And I’m like, “Oh cool, I added an exclude parameter for my situation.” And Brent meant for Aaron to put the first version in. And I kind of – I don’t know, is jumping the shark the correct term?
Erik Darling: Gun maybe?
Ronald Dameron: I jumped the gun and I said, “Oh, here’s my version.” And Brent’s like, way to go, Ron. And then I had to do something else and actually figured out how to do a pull request on Git. That’s the only time I’ve done it so I need to do that some more. But the sp_foreachdb is like my original version, like version one that’s in the first responder kit, because I haven’t incorporated that into here. [crosstalk]…
So to lockdown a database, you’re going to need that sp_foreachdb. You’re going to need it to add a controlled sysadmin group, and I’ll talk about what that means. You’re going to have to reduce permissions to your existing DBA groups. And our DBA’s permissions were managed by Windows groups. You need to deny access to the user database schemas. Then you’re going to drop the DBA groups from the sysadmin server role. Then, you’ll need to grant some DBAs additional permissions, which some of that I found out the hard way, after the initial implementation.
sp_foreachdb, that was created by Aaron Bertrand long ago because he found out that Microsoft provided sproc, stored procedure, would actually skip databases; which is obviously not a good thing if you’re doing this in production. My situation was I needed to be able to exclude certain databases on the box. So we actually, all of our machines had a utility database on it that had a bunch of internally written stored procedures that did backups, you know. It was like what Ola Hallengren’s scripts do that somebody had written back when SQL Server 2000 was out. It was written [inaudible]. So, that, and more.
So I had to exclude that from – it could lock DBAs out of that, you know, that would be stupid. So next, I had to add a controlled sysadmin group. And what I mean by that is that’s a group that held the logins that were managed by the privileged identity manager – that were managed by your password vault for – they also call it – the product was called CyberArk. So in order to gain access to an ID, you had to have a valid change or incident ticket. And they figured out an integration with our – what was it – BMC Remedy, which I used to hate. Remedy change management system – don’t get me going on that one.
Erik Darling: I’ll try not to.
Ronald Dameron: Oh gosh, that’s a full-time job just getting change tickets through. But as long as you had a valid change or incident ticket, you could get promoted to a sysadmin.
Then we had to reduce DBA permissions. Hopefully, I don’t break anything. I didn’t turn off Dropbox, but I guess I’m okay. I’ve got 100MBPS now. So what we did is we reduced the DBA permissions to CONTROL SERVER, and we had to DENY ALTER ANY LOGIN and DENY ALTER ANY CREDENTIAL, because the DBAs weren’t responsible for that, and obviously we didn’t want somebody to be able to create a login with sysadmin rights, do something bad and then be able to remove the login. Although we probably would have caught it anyway with the Guardium tool. We had to GRANT CREATE ANY DATABASE. And usually, in a live setting, I would ask, why do you have to GRANT CREATE ANY DATABASE? Well, you need that to be able to restore a database. Then obviously, on 2012 and above I think it was, we had to DENY ALTER ANY SERVER ROLE, because again, I don’t want DBAs, after I’ve yanked any permissions from them, being able to create their own role with sysadmin rights.
Now, there’s something I found out the hard way, you know, did I miss anything? DENY IMPERSONATE. So when we first – and I did another version of this talk for the security virtual chapter when it first started, and there was a guy on there, Bob [Boshman], who I’m pretty sure worked for Microsoft for umpteen years, and a super nice guy, and he sent me an email saying, “Hey, I saw something missing from your script that you better add in.” And I said, “Yeah, I know what it is, it’s DENY IMPERSONATE.” Because the first set of boxes that we tested on in QA, about 30 boxes, I ran the script and I ran it as myself, and I’m a member of all these BBA groups that are going to lose permissions. So it got like part way through the script and then it started erroring out. I’m like, what the heck’s going on here? Then I try to log back into the boxes that I was changing and testing with; I can’t get into it. Then I’m like, uh-oh, how do I get back into these things? Then I figured out that I hadn’t denied IMPERSONATE. So I executed – I knew another user who still had permissions, so I executed as that user and granted myself sysadmin rights again and fixed all that stuff – then obviously documented that the script has to be run as an ID that’s not going to lose permissions.
So we were using – it became part of the build scripts for a box as it moved to production, and it was run by the enterprise jobs schedule, because that particular entity always has sysadmin rights on the box.
Erik Darling: So there is one question over in Slack. “What permissions are required to create a table?”
Ronald Dameron: I would have to Google that off the top of my head right now…
Erik Darling: That’s not off the top of your head if you have to Google it.
Ronald Dameron: I mean off the top of my head, you would have to have like CREATE TABLE PERMISSION – I don’t know, I forget how granular it is. I would go Google it.
Erik Darling: That would have something to do with which schema and…
Ronald Dameron: Yeah, you would have to have permissions on the schema and then I think CREATE TABLE PERMISSION – yeah, because I have that…
Erik Darling: Probably just need SA for that?
Ronald Dameron: No, no, no – like I said, that’s what made this – like I said, this, I thought, was going to be a major league pain to do, and once I did all the analysis of what a DBA does versus what they actually needed sysadmin rights to do and the daily tasks that you do, the majority of them, you don’t really need sysadmin rights. You need lesser permissions and CONTROL SERVER took care of that, and was the easiest way to take care of it. I mean, I could have been more painfully detailed about it if I was asked to be but this was as far down as I took it. And when I was figuring this out, I actually found – if you Google SQL Server permissions poster, Microsoft is producing these big PDFs that show you…
Erik Darling: Enormous…
Ronald Dameron: Yeah, I mean you can like wallpaper your cubicle with them. But they helped me understand pretty quickly what I needed for the requirements that I was given. I can’t say that – I was trying to figure this out by reading a bunch of different documentations and I came across these and I was like, “Ooh here we go.” So a picture is worth 1000 words, I guess.
So the next thing that was – the requirement was that they didn’t want DBAs to have access to business data. This is where I used sp_foreachdb and CREATE, RETRIEVE, UPDATE, DELETE was removed from all the user schemas. I excluded our utility database, and then we also had a trace database. And interestingly enough, while I was getting ready for this, I’m like, “Wait a minute here…” Because when you were doing your presentation, I was like – I’ve never looked at the query plan for when I run this particular script on a box, so I was like, “I wonder what it does.” Then I was Google-ing on CONTROL SERVER. I was trying to remember when CONTROL SERVER was introduced – 2005. And then I was trying to remember – then there’s another permission now, DENY ALL USERS SECURABLES, which I think was 2014.
I found a blog post by somebody else who was kind of talking about a similar topic who said, “Hey, could you use the DENY ALL USERS SECURABLES permission to keep your DBAs from snooping around your databases? Well, the problem with that was MSDB. That’s another user database that DBAs need access to, and if you DENY ALL USERS SECURABLES to your DBAs, there’s a bunch of things they can’t do. So that’s not going to work.
Erik Darling: So this is why no-one does security. It’s just too hard.
Ronald Dameron: Oh yeah, well like I said, it’s a pain in the butt. I mean, this was a project for a year. This was on two of my performance reviews. That’s how long it took. So we had hundreds of servers – thank god, we only did this in production, so you know…
Erik Darling: Including other testing?
Ronald Dameron: yeah, you know…
Erik Darling: Test it in production?
Ronald Dameron: Oh no, we didn’t test it in production. We tested it in our QA box. So the QA box has got it applied and the production boxes, but not the development boxes; that would have been too cumbersome.
Erik Darling: It brings up an interesting question. How did you refresh development environments if you didn’t also lock them down security-wise; because obviously developers need something to develop on?
Ronald Dameron: So they had another tool. Now, depending on what the data was – I had created, at some point, I know when I supported [the investment line business], I had set up some jobs that would actually restore production – and make smaller ones, obviously – restore production data to a development box periodically, just to refresh it. But then it got to the point where the IT risk and security said no, that’s got to stop or it’s got to go through a process where we vet the sensitivity of that database. They created a data masking factory using IBM Optim tools, which thankfully I didn’t work on anything that had to go through that process, because I heard that it was kind of difficult to get through it. But anything coming from production, supposedly, you couldn’t just do a direct production to development and restore without some kind of scrubbing; especially; obviously with [crosstalk], you’ve got social security – again, investments, we didn’t really have – it was investments for the company.
Erik Darling: Okay.
Ronald Dameron: So how they were investing their money and all that kind of stuff. So I mean yeah, it’s sensitive data but it’s not like it’s PII or PHI. So that’s how that was handled. But what was interesting is, as I was looking at, checking and rechecking some facts and getting ready for today, I thought, “Wait a minute, do I exclude MSDB in my script?” And I search through my script and I’m like, “Hmm, I have not excluded it, yet everything works.”
So I started thinking about how we did stuff at the insurance company, and the database backups were automated by another tool run by a different user. We didn’t – they were run by a scheduler, so I’m like, okay the scheduler has access to MSDB. So it worked out fine. We didn’t catch it back then, I just realized it today. So your mileage may vary depending on how you do your tasks and how automated you are as far as what tools you have at your workplace.
So the next thing you’re going to do is drop the DBA groups from the sysadmin role. That’s after you’ve granted permissions to the other permissions. Like I said, you need to execute this as a user that will not lose privileges. And then I saw – there’s a cursor in my script, please forgive me. But I saw – I think it was at PASS Summit in 2013 – I saw online that Kevin Kline and Aaron were doing this talk, and it was like top 10 development tips that every developer should know, or whatever. Well, he explained that if you just declare a cursor with no options, then you’re getting this full blown – you’re getting every possible option that you could have in a cursor. But if you specify local and fast forward, you kind of trim down the overhead that that cursor incurs, and it runs 70% faster.
Let’s see – can you see that particular…
Erik Darling: I can see it.
Ronald Dameron: Okay, so what’s interesting is that on the left – this is just me running it on my laptop at the time. On the right is before I put the local fast forward in, and it took two seconds to run it on a couple of AdventureWorks databases. Well, when I added local fast forward, it took zero seconds. The thing that’s important about that is this script – the run time is going to be dependent on how many databases are on the server and how many schemas. So that kind of saving, I think, is going to help you.
Okay, so here in my notes, it’s 2013 PASS Summit, 10 Query Tuning Techniques Every SQL Programmer Should Know. So Kevin Kline and Aaron Bertrand did that, and that’s where I got that tip from. Now, I also had to grant some additional permissions, and these, like I said, I found out the hard way. The initial implementation, again, what’s funny about it was I said hey, we’re going to do this, hey, we’re going to do this, and then a lot of times in big companies they’re like, “it’s not going to happen.” Well, it got to the point where it was starting to happen. And it was funny, I started getting a lot more feedback from the teams that weren’t talking to me previously, or were not quite as forthcoming. And it turned out that we needed to grant them the SQL agent operator role so they can manage SQL agent jobs. Because the initial implementation of this, they couldn’t see SQL agent in management studio. And at the time, we had a lot of log shipping in place. So we need to have access to SQL agent to manage log shipping, obviously.
And then we also used SSIS admin to manage the packages. And then sadly, after a few more integrations, we came across a server that still was running DTS and I had to add the DTS admin permission grant to the script. So like I said, a picture is worth 1000 perms…
Erik Darling: What would you know about perms?
Ronald Dameron: Perms, right – I know, exactly, good one. This is like just the top left corner of this permissions poster. And it showed me everything I needed to know and helped me understand what the security model looked like. I mean, god forbid you didn’t need to do this in your own workplace. It does help. After all the analysis that I did, it came down to, for our situation, granting CONTROL SERVER, and a few denies was the solution. So we’ll do a quick demo. It looks like I’m doing good for time.
Erik Darling: Luckily for me, god actually does forbid me do security. So according to Brent, we can’t touch it. So I’m very happy I don’t have to deal with this…
Ronald Dameron: Again, you guys are a small company – oh, you mean you don’t touch it for your clients?
Erik Darling: It’s explicitly out of the contract.
Ronald Dameron: I mean, I spent a fair amount of time the last few years having to deal with this. I mean, I helped install Guardium, I helped install a vulnerability assessment tool, configure it, used some more PowerShell to do that. Security, it can be a full-time job, especially in these big companies and given all the craziness that’s going on these days. You can definitely make a living out of it.
Before we go look at this script, I want to give you some wise words from a guy named Buck Woody…
Erik Darling: Ooh, one of my favorites.
Ronald Dameron: I stole this from him, but I always give him credit. It says, “For people who need this sort of thing: never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or virtual machine, not a production system. Yes, there are always multiple ways to do things and this script may not work in every situation for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Avoid where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion, diarrhea during the operation of this script, see a physician immediately.” And that’s the wisdom of Buck Woody.
So we have serious Buck Woody on the left and not so serious Buck Woody on the right. He’s a great guy. And when I showed that script disclaimer at the last SQL Saturday in Orlando, people were like – I mean, they were bursting out laughing and I was like, “You guys have not seen this?”
Erik Darling: Buck Woody is a hidden gem.
Ronald Dameron: I’m telling you, he’s a great guy. So let me go over to management studio, which is over here – can you see management studio now?
Erik Darling: We’re good.
Ronald Dameron: Okay, so I actually included that here, and then some notes. This is from SQL Saturday Orlando – and again, it’s six steps that I just want to go through.
So, sp_foreachdb, and the caveat is I haven’t put the latest one in from the First Responder Kit. This is where I added the exclude list, because I had to – go ahead…
Erik Darling: No, cool, looks good.
Ronald Dameron: Cool. It was easy because I just cloned, you know, the statement above that. Then, yeah, so I had to add this parameter here; so again for the exclude list. What else – I have a step in here where I have to add the DBA groups that are controlled by the password manager. So that’s commanded out for the demo because I don’t have a domain controller running on this thing. But it’s there for your reference, in case you’re – I would imagine, hopefully, you’re using Windows authentication to manage access to your database servers. That’s what we did. So there’s – and this is set up… When I had to do this, this was set up to deal with 2005, 2008, 2012, and I just actually tweaked it some because I was testing for – I changed it to test for SQL 2012 or higher. Now that we have 2014, 2016, 2017 – and I see a typo, “higer.” I’m not going to change it.
So here’s where I set up granting minimum positions. Now, initially I did not include DENY IMPERSONATE, and I would say once you’re certain that you’ve got your environment created correctly and you’re comfortable with permissions, I would add that in here. And this is where we’re doing – and I have it set up to run against SQL logins. And if you’re going to run it where you need Windows groups, you’re going to need to un-comment this WHERE clause. But I’ve made these notes at the top, so depending on how you’re authenticating, you’re going to have to adjust this; minimal changes.
And then here’s where I deny access to user database schemas, and this bit of code here was a lot of fun with all the frickin quotes. Then like I said, we’re trying to deny ALTER, SELECT, INSERT, UPDATE, DELETE, execute on schema, and then you know, not like trying to keep you stomping on the things that are supplied by Microsoft, or your own schemas. And then here’s where I run sp_foreachdb. There is my exclude list. And then just this is pretty straight forward, drop DBA groups from the sysadmin server role. And then depending on the version, you know, you alter server. If it’s the lower version, then it will do a drop server role member. And then here’s where I had to add SQL agent operator role. This kept growing, you know – it started out with SQL agent operator role, then I had to ask SSIS admin, then DTS admin. This is where the cursor is. There you go.
Erik Darling: Let the cursing begin…
Ronald Dameron: Yeah, so then you just run it. And it helps if you don’t highlight something when you run it. See, I was playing around with execution plan…
Erik Darling: No one wants to get the plan for that.
Ronald Dameron: Yeah right, it takes longer because I had that SELECT in. Come on, there it is. So this is the kind of output that you’ll see. I have it set just to print, but this is what it generates. I have two DBA groups to find on the box, so it’s granting CONTROL SERVER – so first it takes care of the server permissions on that particular box, and that’s what this part is. Then it goes through…
Erik Darling: Oh, cool.
Ronald Dameron: Yeah, and then it goes through every database. So you get the name of a database and then whatever schemas it finds. So here’s AdventureWorks, and it’s restricting access to dbo, guests, human resources, purchasing production, purchasing sales, and the same thing with this database here. And then it just runs that alters for the extra permissions, and boom, you’re done. So let’s go back to – any questions on that?
Erik Darling: It doesn’t look like it. Got a pretty active Slack chat going on. All the stuff you’ve been talking about, I’ve been trying to get links going in so everyone can…
Ronald Dameron: And I have links at the end here. So just to recap: with 2005 onward, you definitely can create a configurable security model for whatever you situation is at your workplace. If you’re concerned about – you have issues or concerns about who has access to your production data. It’s that one script to lockdown the databases. Your servers will be more secure, your auditors will be happy. Hopefully, hackers will be not so happy. And then these are the resources I used. And then permissions poster, again, that’s from like an earlier version of SQL Server; they keep updating it.
At PASS Summit 2012, Don [Keeley] was talking about the evolution of security in SQL Server 2012. He coined the phrase – he called CONTROL SERVER virtual sysadmin. It really is. And the important part about it is sysadmin, I can’t deny you any permissions, but as a CONTROL SERVER, I can – if you have just CONTROL SERVER, I can deny you permissions. So you can limit your DBAs access to production. Then I also saw, at PASS Summit 2010, when Lara Rubbelke and that Si Se Puede, achieving separation of duties with SQL Server. That’s when they unveiled that first whitepaper. And then that article about CyberArk just talks about privileged users. I mean, those are the big targets nowadays for hackers.
I also found some good stuff from another MCM, Andreas Wolter, on his blog. He does some good coverage of comparing CONTROL SERVER to sysadmin SA permissions. And then a link to the top ten query tuning techniques that I got the cursor hit form. And then Krebs on security, if you’re interested in security, Brian Krebs writes a really good blog. He used to work for the Washington Post and then he got so big that he went out on his own. And he’s got – it’s not SQL Server typically, it’s focused on security at a higher level, but it’s a really good read. And there’s another article from SQLmag about database security.
Erik Darling: Very cool.
Ronald Dameron: so, that’s all I have now, sir. That was fun… [crosstalk]
Brent Ozar: Very nice. Well thank you, Ron, appreciate you doing the session. That’s very cool.