Category: Microsoft

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: 
  1. Ensure that SQL Server is set up to allow Server Authentication
  2. Create the LOGIN (in the SQL Server Instance)
  3. Create a USER corresponding with the LOGIN (in the Database)
  4. Grant the USER CONNECT privilege (happens by default, but can be revoked)
  5. 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.

Another video – Splitting an Access database

When I’m making something I sometimes learn new things. They say “you should never stop learning”. I agree with “them”, whoever they may be.

While I was working on the SOPAG project, I investigated “splitting” the Access database into:

  • Logic and presentation, and
  • Data (database definitions and data values)

Components. I knew this could be done, but I had not spent much effort on it before.

The splitting itself was a straightforward enough exercise. Most of the work is done by Access itself. However, you might want to confirm that all the decisions it has made are sensible!

I decided to document the results for my own benefit, and then decided to convert a scrappy Powerpoint presentation into something a little more presentable to upload to YouTube.

Here it is: Splitting an Access database

I had fun doing the work to find out how it worked, and fun making the video. I hope you get something from watching it.

Almost my first video on Youtube

Sometimes things don’t go quite as I intend. A little while ago, someone approached me with a potential project. Unfortunately I was too busy at the time to take it on.

The idea had tickled my fancy. It bubbled away in the back of my mind and as I had odd moments I created bits of it, as what I would describe as a proof of concept. It was a useful exercise in that it has reminded me of a few things, taught me some things about what Microsoft Access is good at and some things it is not so good at. Inevitably, there are some things I would do differently if I did it again. That’s all right, because after all it was only a proof of concept, and there was no real input in the form of “requirements” anyway.

Having produced the thing, then I wanted to show it to an acquaintance. I messed about with a few things and after a couple of iterations, produced this:
SOPAG – A simple Access Application

Having produced the video, and decided to write a “business related blog” it seemed appropriate to share it here.

I wouldn’t claim either SOPAG, or the video are marvelous, but I’ve learned a lot from both of them. In fact, I have set up a little project to take them both a little further.

But that is for the next instalment!