The Basic Security Model in SQL Server – Skill #3

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.


About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

3 Responses to The Basic Security Model in SQL Server – Skill #3

  1. John Marsing says:

    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.


    • way0utwest says:

      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.


  2. Pingback: Map a Login – Basic Skill #3 « Voice of the DBA

Comments are closed.