SQL Server security is a big topic. If you don’t know much about it then it will take some time to first understand some concepts before jumping into setting the security for your database.

The website SQL Server Central has a “Stairway” (series of articles) on this topic. The first article is called Overview of SQL Server Security which was written in 2014 by Don Kiely.

Windows security and SQL Server security have the same two stages. Both allow a user or other login to access the protected resources within the server. The Stairway article lists and describes the two steps as follows:

Authentication: Authentication is the process of positively identifying a person by requiring her to prove that she is the person associated with a login. It answers the question, Who are you? A user logs in, is validated, and gains access to the server. Proving who they are usually is done by providing the password that goes with a user name, but other forms of proof are possible.

Authorization. A user is able to interact with only those SQL Server objects—such as databases, tables, and stored procedures—for which she has permission. Authorization answers the question, “What are you allowed to do?

The website goes on to say the following: “A user might be able to log in to SQL Server, but unless they have permission to do something, such as access a database, it won’t do them much good. So you not only need to provide your users with authentication credentials, you also need to authorize them to access data by defining accounts for them in each database they need to use once they’re authenticated.”

SQL Server takes a very granular approach to security. A user need to be able to log on to the SQL Server. He then needs to be able to get access to a database. After that he needs permissions for the objects (tables for example) in the database. The diagram I created below summarizes the three main steps: login, user and permissions.

• SQL Server – SQL Login
• Database – SQL User
• Objects (tables…) – Permissions

Permissions

As you can see from the screen shot below there are several options when defining table permission. In the diagram we have given Tom45 the permission to read the table. He has permission to real ALL of the columns in the table. We can restrict him to only reading some of the columns in a table if we need to. He for example may be looking at a list of employees for which he is not allowed to see their salary or social insurance number. We can achieve this by un-selecting the check mark and clicking on the button Column Permissions and selecting Grant for the column we want the person to see.