Product articles SQL Monitor SQL Server Security Monitoring
Monitoring Changes in Permissions,…

18 October 2018


Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

18 October 2018


Monitoring Changes in Permissions, Users, Roles and Logins

Phil Factor uses the default trace and a SQL Monitor custom metric to alert you to unauthorized changes in security membership or permissions in any of your monitored databases.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Compliance means keeping a close grip on any changes to the permissions and access control of a database. Sadly, the law has had to acknowledge, from bitter experience, that it is not just external intruders who want to do this, but it could also be attempts at fraud or data theft from within the organisation. Permission changes are certainly one of the things that security experts advise you look out for; you need the equivalent of CCTV trained on your servers.

If you have default trace running, then you should already have an impressive audit, with a record of what changes to permission, users, logins or roles were made, when and by whom.

After the alert

The most important requirement is to get an alert at the point these changes start happening. We’ll set that up shortly in SQL Monitor, using a custom metric that queries the default trace.

When you receive an alert, you can then examine the details of the access and permission changes at your leisure, so you can understand more about the attack. Fortunately, the default trace can go back weeks, depending on the workload, and on the extent of telemetry and monitoring that you have running.

There is a lot of data in the trace, and it is easy to get a bit overwhelmed with it, so we’re aiming to distil it into a simple report of when the change was made, who changed what, and details of the affected security objects. Figure 1 shows the result of our forthcoming labours. I generated the report after creating and deleting a few logins, roles and users.

Figure 1

The action column that you see is just something I’ve added to assist the viewer; it’s a summary of useful data from other relevant columns.

Collecting the data

In previous articles on monitoring SQL Server for database drift, configuration changes and ‘suspicious’ errors, I’ve mined the data I need from Extended Events. Here, I’ve resorted to using the default trace.

Microsoft say:

‘This feature (the default trace) is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.’

Unfortunately, I can find no equivalent way, using Extended Events, of detecting changes in permissions that covers all the range of SQL Server versions in production use. You will get a lot more data from SQL Audit if you have it, but the reason that I like using the default trace is that its presence in SQL Server goes back in the mists of time, and so you are likely to be able to use it. It is still there and smiling in SQL Server 2017, and begging to be used. As a bonus, towards the end of the article, I’ll even show you how to use the default trace to detect unauthorized changes to database objects (tables, view procedures and so on), as we achieved with Extended Events previously, but now shown alongside permission changes. You can see both types event in one single ‘narrative’, or time-based, sequence.

Listing 1 shows how to create the SeeAccessControlChanges function that will collect all the data we need from the default trace. I’ve reduced the data to be scanned to just the period you select, and I’ve just chosen to collect only the records that involve a login, user or role. This filtering is almost disarmingly simple, since each of these provide a value in the TargetLoginName column, whereas for everything else it is NULL.

The only downside is that it takes a half-second or more to get the information, because you are basically reading it from a file. However, in my experience, Extended Event ring buffers are generally slower to read.

Listing 1

Now, when we want to drill into the details of any changes made, we can simply use Listing 2, to execute the function, and it produces a report like the one in Figure 1.

Listing 2

In this example, I’ve just selected all changes that happened in the past day, but you can search a long way back with the default trace, or just a few seconds if you need that.

This approach is, however, more like an audit, a way of assessing the damage. What you really want to know is what is happening right now, because you would rather nip an attack or uncontrolled change in the bud, before the perpetrator has got to the data. You want a baseline from which you can judge that activity is unusual. What you need is a monitoring tool.

Creating the custom metric in SQL Monitor

We next set up a custom trace for SQL Monitor that will alert us as soon as a change occurs. SQL Monitor wants a SQL batch that returns a single integer, and we will choose to return the number of changes to logins, users or roles that happened in a ten-minute interval. As scanning the default trace takes time, we don’t want to be calling it too often. Once every five minutes seems about right to me.

Listing 3

If you get false positives, you can easily fine-tune the WHERE filter in the SQL Expression. You might, for example, get permission changes as part of a legitimate routine within a user database, a third-party tool or perhaps a scheduled task; and these must be filtered out once you are clear about the circumstances in which the event occurs.

If you don’t have SQL Monitor, you can run this on a schedule in SQL Server Agent, and set it to alert you if there are any changes in the permission system involving either logins, roles or users.

The disadvantage of relying on SQL Agent is that, to comply with legislation such as SOX, the monitoring system should be outside the control of what you are monitoring. An attacker who gains sysadmin privileges can disable a SQL Agent job. Also, you want to be able to check the whole farm for any signs of intrusion or uncontrolled changes.

Figure 2 shows us adding a new custom metric to our SQL Monitor installation, which uses Listing 3 to collect the data.

Figure 2

I’m collecting the data every 5 minutes, from the master database, on all my servers

Figure 3

Finally, we add the alert.

Figure 4

Detecting the changes

We can now run various tests and see if some simple changes to logins, roles and users are detected. First, we will make some simple changes. We’ll add a random time interval between DCL commands to simulate real usage a bit closer, as if the commands are being keyed in.

Listing 4

In the Analysis section of SQL Monitor, I’ve created a tile that plots the values for our new Permission/Security changes metric, along with those for the three custom metrics that I’ve written about in previous articles (referenced above).

Having created the tile, run Listing 4 to make sure it detects these untoward database security changes.

Figure 5

You should also find that an alert was fired when it happened.

Figure 6

Now, of course, you should retrieve the detailed report that tells you who made what changes and when, as described earlier.

Using the default trace to relate database drift to permission changes

Sometimes, you want to use the default trace to detect object changes alongside security changes. The following SeeDatabaseObjectChanges function collects this data form the default trace, similarly to the one we used earlier.

Listing 5

And this is how you might want to execute this.

Listing 6

This shows us which logins changed which objects, in which databases. Depending on what you were doing on the server at the time, the reports looks something like this:

Figure 7

This is fine, but we can do even better now. We can mix information from this TVF and the previous one, SeeAccessControlChanges, to see both object changes and permission changes. This gives us the fuller story of what the intruder is doing.

Of course, in this case, the ‘attack’ makes no narrative sense, because I’m simply reusing existing test scripts! The narrative from the real intruder will make more sense.

Figure 8

And this is the code that worked this magic.

Listing 7


The combination of a good monitoring system and the default trace can tell you when something untoward is happening with a server and will provide a baseline of the activity you are monitoring. The default trace is antiquated, but that means that you can manage a whole estate of servers and use the same forensic tools on all of them. Be aware, though, that it is flagged for removal, to be replaced by equivalent functionality with Extended Events. I suspect I’ll rewrite this article then!

The great thing about using the default trace is that your subsequent queries, once SQL Monitor has alerted you, are relatively easy. If your default trace is getting filled rapidly, you may need to search more than the current file. This will make it slower but requires only a slight alteration to the code.

If you haven’t many servers to look after, you can simply use the query to determine how many permission changes had happened in the past ten minutes and fire a SQL Server alert when something is detected. However, you lose the baseline (zero, you’d hope in this case, but don’t count on it!) and you cannot aggregate servers into groups.

Worse, you lose the advantage that SQL Monitor is a remote process that is hard for intruders to turn off, even if they manage to gain godlike powers for their SQL Server user, and even if they try, it fires an alert! I like it most for seeing those spikes in the tile graphs, and an at-a-glance view of the sort of activity that is going on. Graphs can make it easy to assimilate a variety of information and integrate it. They are, in other words, brain-friendly.

  • Max Shapiro

    Is this code only for SQL 2017? The FUNCTION dbo.SeeAccessControlChanges does not seem to compile in SQL 2012

  • Max Shapiro

    Is this code only for SQL 2017? The FUNCTION dbo.SeeAccessControlChanges does not seem to compile in SQL 2012

  • Phil Factor

    @@disqus_nuQC4lr6Ll:disqus There is no good reason I can think of why it shouldn’t me changed to run on 2012. It uses the DateTime2 datatype for the parameters, which can be changed for DateTime. Unfortunately I no longer have access to a 2012 version for testing. Can you tell me the error and I’ll investigate.

  • Michael Spurlock

    The error I get is:
    “Msg 8116, Level 16, State 1, Procedure SeeAccessControlChanges, Line 37 [Batch Start Line 3]
    Argument data type datetime is invalid for argument 1 of switchoffset function.”
    I tried changing some types around, but got the same error.

  • Phil Factor

    @michael_spurlock:disqus @disqus_nuQC4lr6Ll:disqus Try CONVERT(datetime2, SWITCHOFFSET(CONVERT(datetimeoffset, StartTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
    –try this code. It is supposed to work in 2008 but I can’t test it easily

  • Yakov Shlafman

    Good day Phil,
    thanks for your article and sharing.
    I decided to implement your solution for our SQL Servers – 2008, 202 and 2016.
    Listing 1 – the ObjectType column, I could not find description/meaning for the column values, like what is P, RL, SL, DB.
    I did add category name in your query:
    ADayBack.datetime_local as ActionDateTime,
    ADayBack.Data as ExecutedStatement,
    CT.[Name] as CategoryName,
    CASE WHEN ADayBack.ObjectType IS NULL THEN ” ELSE ADayBack.ObjectType END AS ObjectType
    FROM dbo.ufnSeeAccessControlChanges
    (DateAdd(DAY, -1, SysDateTime()), SysDateTime()
    ) AS ADayBack
    LEFT OUTER JOIN sys.trace_categories CT ON ADayBack.category_id = CT.category_id
    ORDER BY datetime_local;

    Could you please help to clarify:the ObjectType column, I could not find description/meaning for the column values, like what is P, RL, SL, DB.


    • Phil Factor

      These are the same codes as used in the sys.objects table. This means AF = Aggregate function (CLR),C = CHECK constraint,D = DEFAULT (constraint or stand-alone),
      F = FOREIGN KEY constraint,FN = SQL scalar function,FS = Assembly (CLR) scalar-function,FT = Assembly (CLR) table-valued function,IF = SQL inline table-valued function,
      IT = Internal table, P = SQL Stored Procedure, PC = Assembly (CLR) stored-procedure, PG = Plan guide, PK = PRIMARY KEY constraint, R = Rule (old-style, stand-alone),
      RF = Replication-filter-procedure, RL=Role, S = System base table, SG=Server Role, SN = Synonym, SO = Sequence object,U = Table (user-defined),V = View, EC = Edge constraint, SQ = Service queue, TA = Assembly (CLR) DML trigger, TF = SQL table-valued-function, TR = SQL DML trigger, TT = Table type, UQ = UNIQUE constraint, X = Extended stored procedure
      ….and so on.
      You are right that it is difficult to find where all these are defined. Also, the column is often NULL. That is why I don’t use them and use the action and data columns instead.

You may also like

  • Webinar

    SQL Monitor Overview

    SQL Server Monitoring Specialist James King takes you from getting a simple overview of your entire SQL Server estate to alerting and diagnosis of SQL Server problems.

  • Article

    Where SQL Server monitoring fits into your tech stack

    This article explains the basic components of a tech stack, and the decisions and requirements that affect how and where monitoring tools generally, and a specialist SQL Server monitoring tool like SQL Monitor, in particular, fit into your stack.

  • Webinar

    An introduction to distributed SQL Server monitoring

    As estates increase both in size and complexity, keeping on top of everything becomes both important and difficult. In this webinar, we talk you through the basics of distributed monitoring, and how you can set up a solution to help you keep on top of your entire SQL Server estate, regardless of how complex it becomes.

  • Webinar

    SQL Server monitoring for a streamlined development process

    So you want to streamline your development process? Redgate’s James King will talk you through the process of monitoring your SQL Servers to ensure you are ready and able to efficiently keep track of your estate.

  • Forums

    SQL Monitor Forum

    Real-time SQL Server performance monitoring, with alerts and diagnostics