The SQL Server Security model has a lot of moving parts to it. Some friends of mine are so expert in it that it is their primary function on the DBA team they work for. My friend (and recently minted Data Platform MVP) Kenneth Fisher (b|t) regularly presents on it.
Fear not! There are a couple of key concepts that you have to get down early in order to not drown in all the other stuff.
As with most of my other 101 posts, this occurred to me while riding my bike. Road bike…the only thing that occurs to me when mountain biking is “don’t hit that tree.”
Imagine if you will, that you own a house.
Because you are the owner, you have a key to the house. You can do anything you want in any room of the house. You decide who else gets keys, and what they can do inside. We’ll assume your significant other also has a key and equal rights to all rooms.
Now just for kicks, lets assume you have 2 kids. Suzie and Little Bobby Tables (Bobby for short). Since mom and dad work, Suzie and Bobby also both have keys so they can get inside after school. However, they are not allowed into each other’s rooms, nor are they allowed to change the locks, paint the walls or use the stove. Because reasons.
So, 4 people can enter the house. 2 can do whatever they want, 2 have some restrictions. Simple enough?
In the SQL Server security model the above translates as follows:
- House – an instance of SQL Server
- Keys to the house – Logins
- Mom and Dad – Logins with sysadmin role (God rights…)
- Suzie and Bobby – Logins without sysadmin
- Rooms – databases
You have to have a key/Login, to even open the door/connect to the instance.
You need specific permissions to access a database/room. Bobby is not allowed in Suzie’s room and vice-versa. Neither kid is allowed in dad’s man-cave, but mom is.
Suzie and Bobby need to be granted access to use a database/room, so they are created as USERS in the database. Mom and Dad do not explicitly need to be granted as USERS in any database, because they own the place and can do whatever they want at the server/house or database/room level.
If Uncle Joe is coming for a visit, he would also get a key/LOGIN and access to the rooms/databases he will be using. When he leaves, he gives his key back (USER/LOGIN removed).
If you want, imagine Bobby has a brother and they share a room. In their room there are two beds. They don’t share and they don’t switch. In this example the beds are database objects such as tables. You can grant or deny access down to that level as well.
So, LOGIN is created first (can’t go into a room until you get in the house). USER access for non sysadmins is created next at the database level. Inside the database, you GRANT or DENY permissions to objects in the room (if you choose). You can get very, very granular if your application/employer requires it.
This is high level overview, with a lot of specifics left out. Such as, give LOGINs to Groups (preferred, instead of individuals), server and database roles, etc. Just knowing the difference between LOGIN and USER puts you ahead of non-DBAs who use the terms interchangeably. Login can be either Windows based or created within SQL Server depending on how the instance is configured and your security teams requirements.
Hope this makes your life easier! Comments are welcomed.
Update: My Apprentice astutely made the observation that a physical or virtual server with more than one SQL Server Instance installed would be a “neighborhood” in this example. Would that make a Failover Cluster a duplex? 🙂 Would a multi-tenant database be an apartment building?
Thanks for reading,
