This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.
Users and Objects
The basic security model diagram that I use is the one below. It’s not fancy, but it conveys the basics of security in SQL Server.
From left to right, users or clients are mapped to principals. Those principals are both in the instance (login) and database (user) as well as roles. Permissions are assigned to roles on objects.
That’s essentially what the basic security model should be for most people. There are other types of structures (credentials, certificates, etc), but in terms of the 80/20 rule, here’s what most DBAs should do:
- Create a login for a person (either Windows or SQL Server login)
- Map this login to a user with the same name in those databases that person needs access to. Only pick those databases needed, not all databases.
- Create a role in each database for each group of users/permissions.
- Add the users to this role
- Grant permissions on the objects needed to these roles.
It’s not complicated, and sticking to this simple scheme, and not granting db_owner or sysadmin to logins or users will allow you to implement basic, easy to understand security in SQL Server.
Voice of the DBA 
Although I like your diagram and the five steps you mentioned, however I still find thinking about all of this at once. What would be good is to have a SSRS report that would show all of this stuff.
LikeLike
I think you’re missing a word in the comment somewhere. However I’m not sure how an SSRS report helps here. You quickly get confused with the level of detail if you trace down more than one user/role or one object. I’m not sure how to represent permissions well so it’s easy to understand, but there must be some way.
LikeLike
Pingback: Map a Login – Basic Skill #3 « Voice of the DBA