Skip to content
  • Categories
  • Blog

BeginCodingNow.com

for data analysts & software developers

for data analysts & software developers

  • Categories
  • Blog
Home » Blog » SQL Server Security Introduction

SQL Server Security Introduction

September 1, 2017  in SQL Server  tagged login / security / permissions / user by Mike

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.


Post navigation

  • ← Set up a Windows Server VM in VirtualBox
  • SQL Server Wrong Server Name →

Search Entire Site

Recent Posts

  • DB Browser Full SQL Dump
  • DBeaver Full SQL Dump
  • Top AI Models
  • Best AI LLMs
  • Flask Introduction
  • Protected: 20250418 SDG Project
  • Protected: 20250417_1
  • Protected: 20250416_1 Front-End
  • Protected: 20250416_1
  • Protected: 20250415 Goals
  • Protected: Social Impact 20250413 Upgrade
  • Types of AI
  • Protected: UNGoals and Social Issues
  • Protected: SQLite UN Goals Categories
  • Protected: UN Goal 17 Dependency Maps
  • Protected: UN Goal 17 for Georgian College
  • Protected: UN Goal 17
  • SQLite UN Goals

Categories

· © 2025 BeginCodingNow.com · Designed with Customizr Pro ·