Security

Read more about the article Dynamic Data Masking (DDM) – Mask query results
Microsoft SQL Server

Dynamic Data Masking (DDM) – Mask query results

Enforcement of the new GDPR will begin in May 2018. One of the cornerstones in GDPR is that an organization must be able to protect sensitive personal information. This can be achieved in many ways and at many different levels, from infrastructure (e.g. monitoring traffic) to protecting individual database records. To protect individual data records, some different techniques are available. Examples are encryption and masking. Masking can be done in a permanent way (e.g. actually replacing a name with random characters), or it can be done dynamically (not replacing the original data, but to mask information at query time). SQL Server 2016 ships with a capability to do this dynamic data masking, DDM, and in this article I’ll cover the basics.

(more…)

Continue ReadingDynamic Data Masking (DDM) – Mask query results
Read more about the article Deny updates on a table but allow inserts
Microsoft SQL Server

Deny updates on a table but allow inserts

Sometimes it is nessesary to deny updates on a table, but allow inserts. This is the case when the history must never be changed, for instance in a table with accounting data.

This can be done in two different ways. The first method is to use permissions on the table and make sure everyone is denied update permissions on the table. The other method is to use an “instead of update” trigger.

Here I’ll show both methods.

(more…)

Continue ReadingDeny updates on a table but allow inserts
Read more about the article List SQL server roles by users
Microsoft SQL Server

List SQL server roles by users

SQL Server roles can be used to give users server wide privileges. The server roles are sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin. The mapping between users and server roles can be found in the system view sys.server_role_members.

Here I’ll show a SQL script that lists all users and their server roles.

(more…)

Continue ReadingList SQL server roles by users
Read more about the article A replacement for syslogins
Microsoft SQL Server

A replacement for syslogins

The system table sys.syslogins was marked as deprecated in SQL Server 2005, and is included up until 2012 for backwards compability only.

So what is the replacement of sys.syslogins? As it turns out, there is no exact match in SQL Server 2005 and later. Instead there are various system views that can be combined to gather the same information.

I created a SQL statement to combine those system views into a single view, matching the old sys.syslogins.

(more…)

Continue ReadingA replacement for syslogins
Read more about the article Copy logins between SQL Server instances
Microsoft SQL Server

Copy logins between SQL Server instances

If you are using SQL Server log shipping (or mirroring), you probably want to copy the logins from the primary server to the secondary.

When databases are log shipped, the users go along with their respective databases. But those users are connected to a login on the primary server. Those logins are not copied automatically to the secondary. Users without connection to a login are called orphaned users.

This has the effect that users may not be able to login on the secondary in case of a failover.

One solutions to this problem is to transfer logins manually. That may be the best solution if there are very few logins to manage. But if there are more than just a few logins, an automatic solution is required.

There are many methods available to handle this. In this blog post I’ll combine a couple of the best solutions available, and add a few features that I found missing.

(more…)

Continue ReadingCopy logins between SQL Server instances