SQL Server Blog

Continuing the theme of Users and Logins, I was asked if there was a circumstance in which you would ever require a Login without an affiliated User? The answer is yes, and it’s more common than you think.

There are a lot of people out there who have roles where the emphasis is on monitoring and maintaining the SQL Servers themselves and not touching the databases or code running on the servers. In these cases there would be no need to allocate access to the databases and data. In fact doing so could be seen as a severe security breach. Therefore these guys are given Server level access only and would require no User attached to their logins.

Following on from my last post in which I explained the difference between a Login and a User, I’m going to expand a little and explain the concept I use quite a lot within some of my posts… the “login-less user”.

As we know from the previous post, a Login provides access to the server whereas a User is the access to a database. Based on that it’s easy enough to ascertain that a Login-less user is an account which has database permissions but cannot login in to server as a whole.

In a good many of my demo scripts I create Users with the caveat “without Login” which led to someone asking me the other day what the difference is in SQL Server between Logins and Users. This was actually asked via text which resulted in a somewhat stunted answer but, in essence, an accurate one and therefore I’m going to keep this relatively short as well…

In a nutshell, a Login is SQL Server level and a User is Database level.

This is a fun one because I’ve seen numerous people struggling with identity columns in temp or freshly generated tables.

For example, let’s take a look at the Person.Person table in AdventureWorks:

Quick note to combine the last few sets of posts… can you use Row Level Security on in-memory tables?

We all know that in-memory tables do tend to have a lot of quirks and many things we might want to use don’t seem to, but in this case we’re all good… row level security does in fact work on in-memory tables:

I’ve had this question a few times now so I thought I would follow up my last post on column permissions with this little note about the SSMS implementation.

Basically, I’ve had people ask “Why can’t I add Column Permissions in SSMS? It’s always greyed out.”. This is what they’re talking about:

I’ve done a few posts about Row Level Security so I thought I would make one about Column Level as well as, many times, people want to restrict access to columns as well and believe the only way is through re-architecting their tables or hiding everything behind a view layer.

Now, as a starting point I’ll happily point out that I have no issue with using a View layer, I just feel this is cleaner as views are extra objects, require extra consideration, extra access rights, along with maintenance and possible recompilation around releases etc.

This is a quick post just to highlight a simple concept that many people don’t know exists.

Every DBA should be implementing tough security rules and permissions across their estate, but this can be very hard to do without having the ability to test any security amendments you may be making.

Most people I have spoken to will tend to create a SQL User, allocate / amend permissions, and then they open a new instance of SQL Server Management Studio, logging in as the SQL User, and then test the permissions accordingly.

This follows on from the last post in which we discussed Row Level Security. This post provides a nice and simple example based on the example proffered in the previous blog post.

We have the following query: