Creating a SERVER ROLE created a LOGIN as well.

One of my co-workers came to me the other day and told me that they found their network id as a login on one of our SQL Server instances. Why is that note worthy? We make a point of using AD groups if at all possible. He did a little bit more work and tracked it down to a series of commands that he had run recently. I’ll be honest with you, this was starting to feel like a couple of other cases where I’ve seen database objects and principals show up unexpectedly. One was when I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD group).

In this particular case the command that caused the interesting results was this:

CREATE SERVER ROLE [ThisIsAServerRole];

In this case SQL created a server principal (a login). I’m not going to demo this since I’ve done similar demos in the other two posts. What I do want to talk about is why this keeps happening. If I had to guess it’s very similar to some user processes I’ve worked on. So for example, let’s say you are importing sales data. The first thing you do is get the SalesPersonId for each of the sales. But there is a new salesperson listed that you don’t have in your SalesPeople table. So you have a side path in the process that says Load any missing salespeople first. Now you can get SalesPersonId, then load the sales data. This is basically the same thing. When you create the server role (or in the previous cases a table, or an audit) and you don’t have sufficient information (in this case a principal_id to own the role) SQL has to create one. An easy way to prove this is if you do this a principal won’t be created.

CREATE SERVER ROLE [ThisIsAServerRole] AUTHORIZATION [MyADGroup];

But Ken, I hear you ask, why can’t SQL just use the AD group automatically rather than create a new, probably unwanted, server principal? Well, I just have to ask, which one? I’m a member of dozens, at least 6 of them have permissions on this instance. Which one should it use? The first one? But what if that’s the wrong one? Say the AllDevs group. Now all of the developers have access instead of just the DBAs. So yea, SQL stores metadata as if it’s data and they treat it just as carefully as you treat yours. (Probably more carefully than some of you .)