Security – Using Server Side security with MS SQL Server

There are times when I want to do something, and it gives rise to questions. Often I have to put the questions to one side in order to get on with the thing which is my immediate priority. When that happens, I put the questions to one side with the intention of coming back to them in the future. This is one of the occasions when I have had the opportunity to go back and answer some of the questions.

The questions in this case were:

How does Server-side security work on MS SQL Server? And

How can I control the access different users have to my database?

I’m not (have never been, and do not really intend to become) a DBA (Database Administrator). I understand a bit about “privileges” but in the past I’ve always had someone else “doing it for me”, and in any case , I’ve worked on other databases such as DB/2 or Oracle.

After a little bit of research and a bit of experimentation (fiddling around), I found the answer to my questions. Although it was hardly earth-shattering, the understanding is satisfying.

The key is to understand that in MS SQL Server the “Server Instance” and the “Database” are separate entities. It is possible to have several databases inside the same Server instance. In fact I do it all the time when I’m experimenting. This means that there are two separate things to define:

A LOGIN, which gives access to the Server Instance, and with Server-side authentication, provides the security, and

A USER, which belongs to the Database and is granted the Database privileges (including CONNECT).

Figure 1 Server-side security for MS SQL Server

Summary of the stages:

Ensure that SQL Server is set up to allow Server Authentication

Create the LOGIN (in the SQL Server Instance)

Create a USER corresponding with the LOGIN (in the Database)

Grant the USER CONNECT privilege (happens by default, but can be revoked)

Grant the USER the appropriate privileges on the Database

If you’re interested in taking this a bit further, I’ve summed all this up in a video on YouTube.