Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.

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:

  • 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


Filed under: Blog Tagged: security, sql server, syndicated, TopTenSkills

Comments

Posted by mark.cusano on 6 September 2011

Steve,

Not sure I understand the bullet below.  How do I map a login to a user with the same name?

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.

Thanks,

Mark

Posted by Steve Jones on 6 September 2011

When you create the login, there is a place to map users to the login in a database. The dialog will run the CREATE USER for you. I'll write a post on this specific item, but if you look at the create login dialog box, there's a selection in the left pane for "User Mapping". In this item, the right side will show a list of your databases and the mapped user. By default if you check a database, it will create a user by the same name for you.

Posted by mark.cusano on 6 September 2011

Ah, ok, Thank you.  I was reading too much into it.  I have a bad habit of know what to click on as opposed to knowing fully what it means...if that makes sense.

Leave a Comment

Please register or log in to leave a comment.