SQL Server Fixed Role Permissions

Roles and Permissions

Some of my recent articles have been focused on permissions and security. There is good reason for that – security is important and all too often it is mis-understood.

You can catch up with a couple of those articles here and here.

It is very important to understand who has what level of access within the server and databases on that server. Sometimes we see users being granted server or database access through the fixed roles available in SQL Server. How exactly do you know what permissions those individuals have via role membership? This article will help to reveal the permissions granted to the various roles and maybe a gotcha or two.

Finding Permissions

There is ample documentation on what the permissions are for each of the various fixed server and fixed database roles in SQL Server. Some of that documentation can be found here and here and here. With all of that documentation, you may be surprised to hear that it is not quite as easy to find the permissions of these roles via queries from within SQL Server – with a caveat. I am going to discuss some documented means to retrieve the permissions for the various roles and also discuss the pitfalls of these solutions.

What are the fixed roles again? Just in case you did not see them in the links from the previous paragraph, I will list the various fixed roles here.

Fixed Server Roles Fixed Database Roles
public public
sysadmin db_owner
securityadmin db_accessadmin
serveradmin db_securityadmin
setupadmin db_ddladmin
processadmin db_backupoperator
diskadmin db_datareader
dbcreator db_datawriter
bulkadmin db_denydatareader
db_denydatawriter

These default roles do have a unique set of permissions for each role. As noted, the permissions are documented well enough. Sometimes, it is preferable to just query the system to retrieve a list of the permissions for each role. This is especially true if one is in need of providing documentation for an auditor on who has what permission.

When trying to query for a list of permissions, one may feel as though they have fallen off their rocker just as the granny in this pic to the left.

Never fear, however, for there is a method to find the permissions of the fixed roles. Let’s take a look at what it takes to query the permissions associated to each fixed role.

System Queries

Unlike most principals, where one can query the various system catalogs to retrieve the permissions assigned to the principal, the fixed roles do not expose the assigned permissions in the same way. With fixed roles, there are two stored procedures that have been created to retrieve the permissions. These stored procedures are sp_dbfixedrolepermission and sp_srvrolepermission.

Immediately I have a “rocker moment” for each of these stored procedures. In the documentation there is a note that states the following.

This feature will 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.

When peaking into the secret sauce behind each of these stored procedures, there is nothing extraordinary to how the permissions are retrieved. In fact, both procedures reference a particular object in addition to the catalog view specific to the type of role (e.g server_principals or database_principals. This second object is called sys.role_permissions. Wait, I said there is no direct view or table to query with the permissions, right?

Let’s try to query that table.

Boom! We have just had another “rocker” moment. As it turns out, this table is the secret sauce to the fixed role permissions being accessible via query. This table can be queried from the stored procedures and can be queried direct – if it is queried from a DAC connection. Most will probably not connect to the DAC just to query the role permissions, so what can we do?

Here is a basic script showing what I have done.

In this script, I have taken the results from each of the stored procedures and dumped them into a temp table. Using this temp table, I can now join to this table to get a more complete list of the permissions in effect for various principals. Once that more complete list is made, then it can be handed to the auditors to satisfy them for at least a week before they ask again ;).

Now it is time for yet another “rocker” moment. Look carefully at the output from these stored procedures. Remember the notice that they will be removed (i.e. on the deprecation list)? It seems there is good reason to remove them from SQL Server – the permissions in sys.role_permissions is not being maintained. That is correct! There are permissions listed in the output of these procedures that are no longer applicable!

If the list is not entirely accurate, then what can be done to get an accurate list of permissions? As it turns out, it seems one may need to code a solution that has the permissions hard coded in the script – very similar to what these system stored procedures were doing.

Recap

Capturing fixed role permissions is possible through the use of two system stored procedures. Just like the red telephone booths, these stored procedures are soon to be a thing of the past. These stored procedures are deprecated and may be just as reliable as those old telephone booths.

Too bad there is not a better means to trap the permissions from these fixed roles. It would be really nice to be able to view them just the same as can be done with the other principals (users and logins).

Now go forth and Audit your roles.

PS

What is up with that weird granny pic? Well, it was a challenge from Grant Fritchey to use the image in a technical blog post. You can read the challenge invite over here. And yeah, I know it is some sort of Dr. Who thing.

SQL Server Permissions – Database Roles

securedb

EZ PZ Permission Squeezee

Given the critical level of importance related to permissions, one may think it is a concept that is well understood by all who are given the charge of protecting the data.

There is a great responsibility when the keys to the data kingdom are granted to a DBA. The DBA is responsible for ensuring the integrity and security of the data. To ensure the security of the data, the DBA has the responsibility of granting, revoking, or denying access, at various levels, to the data and objects within the database.

Despite this high visibility and critical nature of this concept, understanding permissions and assigning permissions does not seem to be as straight forward as it should be. Evidence of this is something I see far too frequently in the wild  as illustrated by the following image.

This screenshot is only a part of the problem that I wish to break down and discuss in this article.

SQL Server Permissions

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. In a previous article, I outlined the different flavors of principals while focusing primarily on the users and logins. You can brush up on that article here. While I touched lightly, in that article, on the concept of roles, I will expound on the roles a bit more here – but primarily in the scope of the effects on user permissions due to membership in various default roles.

Let’s reset back to the driving issue in the introduction. Frequently, I see what I would call a gross misunderstanding of permissions by way of how people assign permissions and role membership within SQL Server. The assignment of role membership does not stop with database roles. Rather it is usually combined with a mis-configuration of the server role memberships as well. This misunderstanding can really be broken down into one of the following errors:

  • The belief that a login cannot access a database unless added specifically to the database.
  • The belief that a login must be added to every database role.
  • The belief that a login must be added to the sysadmin role to access resources in a database.

The experienced professional will likely note that there is a direct conflict between a few of these beliefs. That said, all too often I see all three of these misconceptions implemented in every instance for nearly every user.

Let’s start looking at these misconceptions. To investigate these problems, I will create a login. After creating the login, I will add that login as a member to the sysadmin role. Once the login is added to the sysadmin role, I will then run some simple tests within my DBA database.

Sysadmin

The creation of a server principal (login) and adding the principal to the sysadmin role is fairly simple. The next couple of screenshots are followed by a quick script that will perform the same actions.

As was promised, here is the script that will do the same thing as illustrated in the GUI above.

With the user now in place, let’s test. The primary test here would be that a server principal cannot access the database since explicit database permissions have not been granted. Here is the first batch of tests that I will run.

The first statement is to allow me to impersonate the superuser login. From the impersonated connection, I first check to see I can query the sys.objects system catalog. Then I test the database_principals system catalog. Next in line is to check the list of permissions that have been granted to the superuser account. Each of these queries executes successfully without error. Here is a screen grab for these first three tests.

Notice the first two queries returned an empty set. This is not a failure, rather evidence that the select statement ran successfully. In the third result set, we can see that the superuser account has all sorts of server level permissions. In the result set there was not a single database level permission.

The last query that utilized sp_helprotect returned the following error:

Msg 15330, Level 11, State 1, Procedure sys.SP_HELPROTECT, Line 302
There are no matching rows on which to report.

This is confirmation that there is no database user called superuser.

So I can query a database without my server principal being given direct access to the database (it is worth reiterating here that this principal is in the sysadmin server role), but can I do other things such as create objects? Let’s test that with the following script.

This script is straight forward. All it does is check for a table. If that table exists, then drop it and recreate it. The last little bit will check to confirm the successful creation of the table. This script succeeds as illustrated in the following image.

That should be pretty convincing that if you add a server principal to the sysadmin server role then that user has access to the databases. These tests have illustrated that it is not necessary to add a server principal as a database principal when that server principal is in the sysadmin role (an erroneous configuration often seen). If the database principal is not necessary in this case, then what will happen if a database principal does exist?

Database Principal in Every Database Role

The next logical step in the sequence is to create a database principal for the already created superuser server principal. Once created, we will test to see what effects if any can be observed by having this database principal in every database role as well as the sysadmin role. This will help to test the first two bullet items from the list of common configurations I have seen in the wild. Let’s start with the script that will help create the principal to be used during the next iteration of tests.

The script basically creates a database principal and then adds that principal to each of the default fixed database roles available in SQL Server. Those roles are easily viewed in the first image in this article and are also listed here for ease of reference (intentionally ignoring the public role).

  • db_owner
  • db_accessadmin
  • db_securityadmin
  • db_ddladmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

The tests for this round of changes will be just like in the previous section. Here is the script to be used for the first batch of tests.

The major differences between this version of the test and the previous iteration of the test is that I have the table still in existence (I did not drop it but that will come shortly) and I have created a database principal so the first two queries will show a single row for each instead of an empty result set. The next significant difference is the last query that utilizes sp_helprotect. Instead of an error like the first time, this execution gives me the following results.

Next I will rerun the test to create an object with the following script:

This script will produce the same results as in the first example. The table, since it was already there, will be dropped and then recreated. After it is recreated, the validation script will find the table and return a single row.

This series of tests has just shown that a database principal tied to a login which is added to the sysadmin role and then added to all database roles will still be able to do pretty much everything a sysadmin can do. To this point, I have shown how bullet points one and three are not sound in reasoning. With that, there is still the test to prove (true or false) the final bullet point that a principal should be added to every database role.

Before testing the validity of the configuration, I want you to look closely at the list of database roles.

  • db_owner
  • db_accessadmin
  • db_securityadmin
  • db_ddladmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

Just reading the names of these roles should be a good indicator that adding a user to every database role is not a desired configuration. I am sure the question is bubbling up now in regards to some of those “but how is it working in the previous examples?”. The response to that is very easy: “The user is a sysadmin!”.

To test this last bit of the configuration, it is time to remove the server principal from the sysadmin role. I will do that via the following script.

With the user removed from the sysadmin role, it is time to repeat the tests.

The results are significantly different this time.

Msg 229, Level 14, State 5, Line 132
The SELECT permission was denied on the object ‘objects’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 229, Level 14, State 5, Line 136
The SELECT permission was denied on the object ‘database_principals’, database ‘mssqlsystemresource’, schema ‘sys’.

I just tried to query two objects and was refused due to a denial in each case. What if I try to query that table I created previously?

This will give me the following error.

Msg 229, Level 14, State 5, Line 141
The SELECT permission was denied on the object ‘MySuperTable’, database ‘DBA’, schema ‘dbo’.

Well that is not the desired effect or is it? Remember, I added the superuser principal to every role and that includes the “deny” roles.

How about if I try to drop and recreate that table?

Again, no dice!

Msg 229, Level 14, State 5, Line 157
The SELECT permission was denied on the object ‘objects’, database ‘mssqlsystemresource’, schema ‘sys’.

With this constant blocker of being denied cropping up, let’s take a look at relevant permissions related to the database roles to which I added the superuser principal. To evaluate those permissions, I am going to use the following script that relies on the results of a system stored procedure called sp_dbfixedrolepermission. (Word of advice, this procedure is deprecated and I will be writing more on that in a future article.)

Looking at the results of that script, one can start to see why we are having so many problems executing a select.

In this result set, one can see that by virtue of the db_owner role and the db_datareader and db_datawriter roles, we have permissions that are being GRANTed. Immediately after that, we hit the DENY. This behavior is to be expected. A DENY permission will override a GRANT except when the principal is a member of the sysadmin server role.

Seeing this in action, the next logical step is to remove the role membership from the two “deny” roles (in this scenario where all are checked) for starters. Once done, go ahead and re-run these tests, you will see that things will work differently. While you are at it, take it a step further and rerun the tests after removing superuser from the db_owner role. Think about it, do you really need to have db_owner along with all of the “grant” related roles given that db_owner gives all of those same roles?

One last bit of homework is for you to run the tests another time from the point of when the database principal was created. Only this last batch of tests, impersonate the database principal instead of the server principal. You can do that by doing the following:

Instead of using the following impersonation statement:

You may be interested by the results.

Recap

I have taken you on a journey through three common mis-configurations with principals and permissions. It really is quite unnecessary to add a principal to the sysadmin fixed server role as well as every fixed database role. Even if the principal is not a member of the sysadmin role, it is foolish to add it to every database role. There just is no good logic in setting up permissions in that manner.

Take a moment to focus and you can say EZ PZ permission squeezee.

Database Dropped

What do you do when a developer comes to you and asks, “Where did the database go?  The database was there one minute, and the next it was not.”  Only one thing could be database droppedworse than the feeling of losing a database on your watch, and that would be losing a production database. It’s like magic—it’s there, and then it disappears. To compound the issue, when asking people if they know what might have happened, all will typically deny, deny, deny.

What do you do when you run into that missing database situation and the inevitable denial that will ensue?  This is when an audit can save the day.  Through an audit, you can discover who dropped the database and when it happened.  Then you have hard data to take back to the team to again ask what happened.  Taking the info from a previous article of mine, we can alter the script I published there and re-use it for our needs here.

This script will now query the default trace to determine when a database was dropped or created.  I am limiting this result set through the use of this filter: ObjectType = 16964.  In addition to that, I have also trimmed the result-set down to just look for drop or create events.

This is the type of information that is already available within the default trace.  What if you wished to not be entirely dependent on the default trace for that information?  As luck would have it, you don’t need to be solely dependent on the default trace.  Instead you can use the robust tool called extended events.  If you would like to be able to take advantage of Extended Events to track this information, I recommend you read my follow-up article here.

This has been a republication of my original content first posted here.

Audit All Select Statements

audit_selectLegislation and regulation sometimes dictates that certain activities must be tracked within a database. On occasion, it will be required that queries be audited and tracked to reach compliance with the legislation or regulation. To achieve this compliance, this article will demonstrate how to use Extended Events to audit statements being executed within the database.

Over the course of a few recent articles, I have included little hints here and there alluding to this article. In fact, now would be a good time to review one of these articles in preparation for what I will be sharing today. Take a moment to refresh your memory on this article about finding the right event – here.

Backstory

I have to be a little honest here. Prior to somebody asking how they could possibly achieve a statement audit via extended events, I had not considered it as a tool for the job. I would have relied on Audit (which is Extended Event related), or some home grown set of triggers. In this particular request, Audit was not fulfilling the want and custom triggers was not an option. Another option might have included the purchase of third party software but there are times when budget does not allow for nice expensive shiny software.

So, with a little prodding, I hopped into the metadata and poked around a bit to see what I could come up with to achieve this low-budget audit solution.

Low-Budget Audit

Using the handy scripts I have shown previously (and that I just linked to), I was able to explore the Extended Events metadata and find just what may work properly. The requirements in this case were that it needed to be done in XEvents and that it must capture SELECT statements. To find the events that seemed plausible for this task, I plugged the term “SELECT” into my queries as follows:

From this query, there was really only one event that made any sense for my task – “degree_of_parallelism”. There was another event returned in the result set, but it made no sense to me as a possible candidate event for auditing select statements (it was ucs_transmitter_reclassify). From the results, I then took the keyword associated to degree_of_parallelism to see what else might be pertinent. I plugged that keyword “execution” into the following query:

The results of the previous query will be quite a bit more substantial. Some make sense to include in the audit and some do not. I opted to not included any of the events to keep things as clean and simple as possible.

More Requirements

I now have the base events covered that I want to use for my audit. That said, my base requirements are just not extensive enough. When I audit something, I really want to know who did it, when it was done, where it originated and what was it that they did. The degree_of_parallelism event will capture all of the select statements but it does not meet these additional requirements.

In order to meet the additional requirements, I will attach a handful of actions to the degree_of_parallelism event. The addition of the actions will provide all of the data I want and need. Combine the event and actions together, I came up with the following session definition.

This is a very simple session to pull together. I have added a few things in on my predicate to help limit the scope of the session to the AdventureWorks2014 database and to make sure I am not trapping events related to the code completion tools shown. Notice here also that there is a potential chance to optimize this session. Can you find it? Hint: It may be in the predicate. Second hint: re-read this article about predicates.

Now the trick to what makes this work to audit only the select statements is right there in the predicate. I have instructed the session to ignore any statement_type that is not a value of 1. As it turns out, statement_type of 1 is a select statement. To see how these values map, here is a quick query and the correlated results.

dop_statementtype

Based on these values, should you want to audit a different query type, just change the predicate to use the map_key value that corresponds to the desired statement type.

Testing

Testing this session is rather simple. Here is a quick and dirty script to help test it.

Conclusion

Building low cost solutions is a common requirement for the data professional. When given the opportunity, try things out to see what you can build to provide the solution. In this case, I have demonstrated how Extended Events, however unlikely a candidate, can provide a working solution to help audit any select statements occurring within your database.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.

SQL Server Principals – Back to Basics

Categories: News, Professional, Security, SSC
Comments: 3 Comments
Published on: January 28, 2016

securedb

Prelude in SQL Minor

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

SQL Server Principals

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. SQL Server principals come in more than flavor. (This is where a lot of confusion gets introduced.) The different types of principals are database and server. A database principal is also called a database user (sometimes just user). A server principal is also called a server login, or login for short.

Server and database principals are quite a bit different in function and come with different permission sets. The two are sometimes used, in reference, interchangeably, but that is done incorrectly. Today I hope to unravel some of what each is and how to see permissions for each type of principal.

Let’s first look at definitions for each type of principal and how to query for basic information about each principal (e.g. name and creation date).

Principals

In general principals are entities to which permissions are granted. These entities can request access or resources. As mentioned already, these principals can be scoped to different levels. These levels as mentioned include database and server.

Server Principals

Server Principals are the kind of principals that include logins and server roles. You may be familiar with some of the server roles:

  • public
  • sysadmin
  • securityadmin
  • serveradmin
  • setupadmin
  • processadmin
  • diskadmin
  • dbcreator
  • bulkadmin

The logins can be created from a windows login or group, or be created as a SQL Login. These principals can also include custom created server roles (in addition to the system created server roles already listed). Once a principal is created, permissions may be granted to the principal. When these permissions are granted, then when the principal attempts to request a resource (related to the permission), to perform a task, the principal can complete that task.

What permissions can be granted to a principal at the server scope? A list of permissions can be created via the following query:

And a sample of the results could look like this:

server permissions

An interesting note here is in the red highlighted permissions. If you look at the documentation for server permissions you will not find those two permissions (at least not as of this writing).

From the permissions returned by the query, you will see that these are all permissions related to server administration type of tasks. Note that these permissions do not grant the ability to do the type of tasks attributed to database types of actions. For instance, the server permissions do not grant the explicit permission to create a reference, execute a procedure or create a table within a database. These are all permissions reserved for the database scope.

Database Principals

Database principals are the type of principals scoped to the database level. These principals will request resources from the database and depending on permissions granted to the principal be able to perform various tasks within the database. The types of database principals include database roles, application roles, and database users. SQL Server Logins can be mapped to a database user and thereby be granted access to the database as the database principal.

Since a database principal can include the database roles, here is a list of the potential database roles:

  • public
  • db_owner
  • db_accessadmin
  • db_securityadmin
  • db_ddladmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

These roles can significantly simplify security management within the database. One can easily assign a database principal to be a member of the db_backupoperator role and thus grant that principal the ability to backup the database without much more need to grant or deny permissions.

Considering the ease of role management, one thing that bugs me and that I see frequently is the addition of a database user to every database role in all databases. It makes little sense to add a user to the db_owner role and then also to the db_datareader and db_denydatareader roles. For one, db_owner already has the ability to read from the tables thus negating db_datareader. Consider the db_denydatareader now – it is opposite to db_datareader. Why try to grant and deny read access to the same user? It makes no sense at all.

The next thing that pains me about these roles is the public role. I have written about the public role previously, but it needs stating again. Do not grant any additional permissions to the public role. This is like enabling the guest user and opening up the database to everybody. I have seen a rash of granting “alter server state” and “view server state” to the public server level role and it is painful to see. The same advice applies to the public role whether it is at the server or database scope.

Once a database principal has been created, it is time to proceed to giving the principal the necessary access. Here are some of the possible permissions that can be granted (along with a query to find even more):

db_permissions

Within these results, it is apparent that a database principal can be granted the ability to perform backups, create procedures, execute procedures and even create encryption keys. Between the server scope and the database scope, there is decent level of granularity to control access and resources within the database instance.

Finding Permissions for Each Principal

It is not uncommon to need to know and report on who has been granted what level of permissions within the database environment. If you have been with the database since inception to conception, you probably have documentation on every permission that has been granted. If you inherit a database, your odds on good documentation about the permissions is probably significantly lower. I have shared a comprehensive script previously to show all of the permissions. Sometimes a little less info is more than adequate for the current needs.

Here is a quick alternative with just a little less info to provide database permissions and server permissions based on the input of a specific list of users and databases.

Now, this script does require the use of a function to split strings. The one I am referencing was written by Jeff Moden and can be found here. In this example, I am looking at a few test principals that I created – testuser, Phantom, Gargouille and Garguoille (which is invalid). Running the script, I would receive results such as the following:

audit_output2

This is a pretty quick running script to gather report worthy data on principals and permissions.

All about the Change

Comments: 1 Comment
Published on: January 12, 2016

TSQL2sDayThe second Tuesday of January 2016 is now upon us and you know what that means. Well, I hope you know what that means.

It is time for TSQL Tuesday. It is now the 74th edition of this monthly blog party. This month the host is Robert Davis (blog | twitter) and he has asked us to “Be the change”. Whether the inspiration for this topic is the new year and resolutions, or Ghandi (you must be the change), or CaddyShack (be the ball), we will be discussing “Change.”

Specifically, Robert requested that we discuss data changes and anything relating to data changes. Well, I am going to take that “anything” literally and stretch the definition of changing data just a bit. It will all make sense by the end (I hope).

Ch-ch-changes

Changes happen on a constant basis within a database. Data will more than likely be blackbox2changing. Yes, there are some exceptions to that, but the expectation that data is changing is not an unreal expectation.

Where that expectation becomes unwanted is when we start talking about the data that helps drive the configuration of the server. Ok, technically that is a setting or configuration option or a button, knob, whirlygig or thingamajig. Seldom do we really think about these settings as data. Think about it for a moment though. We can certainly derive some data about these changes (if these settings themselves are not actually data).

So, while you may call it settings changes, I will still be capturing data about the changes. Good? Good! Another term for this is auditing. And auditing applies to all levels including ETL processes and data changes etc. By that fortune, I just covered the topic again – tangentially.

How does one audit configuration changes? Well, there are a few different methods to do this. One could use a server side trace, SQL audit, Extended Events or (if somebody wants to) a custom solution not involving any of those using some sort of variation of tsql and error log monitoring. The point is, there are options. I have discussed a few options for the custom solution path as well as (recently published article using…) the default trace path. Today I will dive into what it looks like via SQL Audit.

When creating an audit to figure out what changes are occurring within the instance, one would need to utilize the SERVER_OPERATION_GROUP action audit group. This action group provides auditing of the following types of events:

  • Administer Bulk Operations
  • Alter Settings
  • Alter Resources
  • Authenticate
  • External Access
  • Alter Server State
  • Unsafe Assembly
  • Alter Connection
  • Alter Resource Governor
  • Use Any Workload Group
  • View Server State

From this group of events, we can guess at the types of actions that might trigger one of these events to fire for the audit. Some of the possible actions would be:

Action Example
Issue a bulk administration command BULK INSERT TestDB.dbo.Test1
FROM ‘c:\database\test1.txt’;
Issue an alter connection command KILL 66
Issue an alter resources command CREATE RESOURCE POOL PrimaryServerPool
WITH {}
Issue an alter server state command DBCC FREEPROCCACHE
Issue an alter server settings command Perform sp_configure with reconfigure
Issue a view server state command

SELECT *

FROM sys.dm_xe_session_targets

Issue an external access assembly command CREATE ASSEMBLY SQLCLRTest
FROM ‘C:\MyDBApp\SQLCLRTest.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS;
Issue an unsafe assembly command CREATE ASSEMBLY SQLCLRTest
FROM ‘C:\MyDBApp\SQLCLRTest.dll’
WITH PERMISSION_SET = UNSAFE;
Issue an alter resource governor command ALTER RESOURCE GOVERNOR DISABLE
Authenticate see view server state vsst type occurs for auth events
Use any workload group See Resource Governor

This is quite a bit of interesting information. All of these events can be audited from the same audit group. The interesting ones of this bunch are the ones that indicate some sort of change has occurred. These happen to be all but the “Authenticate”, “View Server State” and “Use any workload Group” events even though these events may be stretched to say something has changed with them as well.

With all of that in mind, I find the the “alter server settings” event to be the most problematic. While it does truly capture that something changed, it does not completely reveal to me what was changed – just that a reconfigure occurred. If a server configuration has changed, I can capture the spid and that reconfigure statement – sure. Once that is captured, I now have to do something more to figure out what configuration was “reconfigured”. This is highly frustrating.

Here’s an example from the audit I created:

audit_alterserversettings

This is only a small snippit of the results. I can see who made the configuration change, the time, the spid, the source machine etc. I just miss that nugget that tells me the exact change that was made. At least that is the case with the changes made via sp_configure. There are fixes for that – as previously mentioned.

Here is another bit of a downside. If you have the default trace still running, a lot of this information will be trapped in that trace. Furthermore, some of the events may be duplicated via the object_altered event session (e.g. the resource governor events). What does this really mean? Extra tracing and a bit of extra overhead. It is something to consider. As for the extended events related events and how to do this sort of thing via XE, I will be exploring that further in a future post.

Suffice it to say that, while not a complete solution, the use of SQL Audit can be viable to track the changes that may be occurring within your SQL Server – from a settings point of view.

Auditing Needs Reporting

Comments: No Comments
Published on: October 13, 2015

TSQL2sDay

 

Welcome to the second Tuesday of the month. And in the database world of SQL Server and the SQL Server community, that means it is time for TSQL2SDAY. This month the host is Sebastian Meine (blog / twitter), and the topic that he wants us to write about is: “Strategies for managing an enterprise”. Specifically, Sebastian has requested that everybody contribute articles about auditing. Auditing doesn’t have to be just “another boring topic”, rather it can be interesting and there is a lot to auditing.

For me, just like I did last month, I will be just doing a real quick entry. I have been more focused on my 60 Days of Extended Events series and was looking for something that might tie into both really well that won’t necessarily be covered in the series. Since I have auditing scheduled for later in the series, I was hoping to find something that meets both the XE topic and the topic of Auditing.

audit_wordcloudNo matter the mechanism used to capture the data to fulfill the “investigation” phase of the audit, if the data is not analyzed and reports generated, then the audit did not happen. With that in mind, I settled on a quick intro in how to get the audit data in order to generate reports.

Reporting

An audit can cover just about any concept, phase, action within a database. If you want to monitor and track performance and decide to store various performance metrics, that is an audit for all intents and purposes. If you are more interested in tracking the access patterns and sources of the sa login, the trapping and storing of that data would also be an audit. The data is different between the two, but the base concept boils down to the same thing. Data concerning the operations or interactions within the system is being trapped and recorded somewhere.

That said, it would be an incomplete audit if all that is done is to trap the data. If the data is never reviewed, how can one be certain the requirements are being met for that particular data trapping exercise? In other words, unless the data is analysed and some sort of report is generated from the exercise it is pretty fruitless and just a waste of resources.

There is a plenitude of means to capture data to create an audit. Some of those means were mentioned on Sebastian’s invite to the blog party. I want to focus on just two of those means because of how closely they are related – SQL Server Audits and Extended Events. And as I previously stated, I really only want to get into the how behind getting to the audit data. Once the data is able to be retrieved, then generating a report is only bound by the imagination of the intended consumer of the report.

SQL Server Audits

Audits from within SQL Server was a feature introduced at the same time as Extended Events (with SQL Server 2008). In addition to being released at the same time, some of the metadata is recorded with the XEvents metadata. Even some of the terminology is the same. When looking deep down into it, one can even find all of the targets for Audits listed within the XEvents objects.

Speaking of Targets, looking at the documentation for audits, one will see this about the Targets:

The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log. Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records.

That doesn’t look terribly different from what we have seen with XEvents thus far. Well, except for the addition of the Security and Application Event Logs. But the Target concept is well within reason and what we have become accustomed to seeing.

If the audit data is being written out to one of the event logs, it would be reasonable to expect that one knows how to find and read them. The focus today will be on the file target. I’m going to focus strictly on that with some very basic examples here.

I happen to have an Audit running on my SQL Server instance currently. I am not going to dive into how to create the audit. Suffice it to say the audit name in this case is “TSQLTuesday_Audit”. This audit is being written out to a file with rollover. In order for me to access the data in the audit file(s), I need to employ the use of a function (which is strikingly similar to the function used to read XE file targets) called fn_get_audit_file. The name is very simple and task oriented – making it pretty easy to remember.

Using the audit I mentioned and this function, I would get a query such as the following to read that data. Oh, and the audit in question is set to track the LOGIN_CHANGE_PASSWORD_GROUP event.

There are some tweaks that can be made to this, but I will defer to the 60 day XE series where I cover some of the tweaks that could/should be made to the basic form of the query when reading event files / audit files.

XE Audits

Well, truth be told, this one is a bit of trickery. Just as I mentioned in the preceding paragraph, I am going to defer to the 60 day series. In that series I cover in detail how to read the data from the XE file target. Suffice it to say, the method for reading the XE file target is very similar to the one just shown for reading an Audit file. In the case of XEvents, the function name is sys.fn_xe_file_target_read_file.

Capturing data to track performance, access patterns, policy adherence, or other processes is insufficient for an audit by itself. No audit is complete unless data analysis and reporting is attached to the audit. In this article, I introduced how to get to this data which will lead you down the path to creating fantastic reports.

Database Settings Changes – Red Handed

Comments: 4 Comments
Published on: July 8, 2015

One of my pet-peeves (and consequently frequent topic of discussion) is finding database settings (or any setting that has changed) without knowing about it. Worse yet is finding that the change has occurred and nobody claims to have any knowledge of it or having done the deed.

This happened again recently where a database was set to single_user and suddenly performance in the database tanked. Change the database back to multi_user and the performance issues are magically resolved.

Fortunately there is a means to combat this. Well, sort of. The default trace in SQL Server does capture the event that occurs when the database is set to single_user or read_only. Unfortunately, all that is captured is that an Alter Database occurred. There is no direct means of mapping that event to the statement or setting that changed.

This inadequacy got me to thinking. The default trace is looking at a set of specific “events”, why wouldn’t that set of events be available within Extended Events. It only seems logical! So I decided to query the event catalog and lo and behold, I found just the event I was seeking – object_altered. Combine this with a recently used predicate (object_type = ‘DATABASE’) and we are well on our way to having just the trap to catch the source of these database changes red-handed.

Easy enough to create this particular session. The event does not capture the “whodunnit” without a little extra prodding. So, I added in a couple of actions to get that information – sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname. Additionally, the event does not explicitly tell me what setting changed – just that some setting changed. For this, I decided to add the sql_text action so I could correlate event to the actual setting being changed. Then to cap it all off, I made sure the predicate specified that we only care about database settings changes as previously mentioned.

Running the session and then testing some settings changes should prove fruitful to capturing good info. Here are a few of the tests that I ran and the results of those tests (by querying the session data).

Now to try and look at the results.

DB Change Data

 

There you have it! I have just been caught red-handed changing my AdventureWorks2014 database to single_user and multi_user.

Bonus

For more ideas on settings and changes and so forth, Andy Yun (blog | twitter) has invited all to participate in TSQL2SDAY on this very topic. He has invited all to talk about their experiences with “default settings” and what you might change them to! You can read about it here. I have another article coming up that will fit just nicely with that. Let’s just call this a preview and maybe it can help you get those tsql2sday juices flowing.

Database Drops in SQL 2012

In the previous article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through the use of Extended Events.

What I forgot to mention is the method I shared was for SQL Server 2014. While the events do exist for SQL Server 2012, there is a minor modification that needs to be made in order to avoid the dreaded error message that says something like:

Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.

I am sure many of us would rather not have to deal with such a terrible thing as an error when we want to do something that should just work. Well, here is the fix for that error if you tried to deploy that XE Session to a previous version (such as 2012).

Do you see that change? Instead of using the map_value in 2012, one must use  the map_key. This was a good change in 2014 to allow us to use human friendly terms instead of needing to lookup the map_key for everything like in 2012.

In following the theme from the previous article, here is the rest of the setup for testing this XEvent session to see how it would trap that data and how to parse the data from the session.

Enjoy!

Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.

xe_results

In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.

Audit who Dropped the Database

In the first article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through querying the default trace.  It is a good enough solution if you have not disabled the default trace.

On the other hand, what if you need to do something more flexible?  What if you wanted to track this down through a more robust tool such as extended events?  XEvents has what you need to be able to properly track these types of events.  In this article, I share how to use Extended Events to capture this kind of audit data.

There are a few things to note with this event session.  First is that I am trapping two separate events: sqlserver.object_deleted and sqlserver.object_created.  The next important note is that I have to enable a flag on both events to trap the database name.  That is done like this: SET collect_database_name = (1).  Last note is something that should be noticed after enabling the session and performing a couple of trials.  The events I am using will fire twice for every DROP or CREATE operation.  This happens due to the ddl_phase for each.  There is one event fired for the start of the event and then another event when the event commits or hits a rollback.  Because of this, I am outputting the ddl_phase in my query to read from the session data.  Let’s run a quick test and see how this data can be queried.

Also of importance is to note the file path for the output file. If the path does not exist or you do not have permissions to the directory, you will get an error message and the session will not create.

Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.

xe_results

In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.  Enjoy!!

«page 1 of 2








Calendar
March 2017
M T W T F S S
« Jan    
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, March 24, 2017