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.
References
- Principals (Database Engine)
- Database-Level Roles
- Permissions (Database Engine)
- CREATE LOGIN (Transact-SQL)
- CREATE ROLE (Transact-SQL)
- GRANT (Transact-SQL)
- REVOKE (Transact-SQL)
- DENY (Transact-SQL)
Filed under: Blog Tagged: security, sql server, syndicated, TopTenSkills 
