http://www.sqlservercentral.com/blogs/steve_jones/2011/08/29/the-basic-security-model-in-sql-server-_1320_-skill-_2300_3/

Printed 2014/07/23 04:07AM

The Basic Security Model in SQL Server – Skill #3

By Steve Jones, 2011/08/29

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.

security

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:

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.

References


Filed under: Blog Tagged: security, sql server, syndicated, TopTenSkills
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.