Public Role Permissions – Back to Basics

Permissions in the database environment is such an important task. Setting permissions correctly is paramount to a successful audit and one of your best defenses against improper/unwanted access. Yet, in spite of the importance, security is often overlooked, neglected, improperly configured or just flat out ignored. Let’s not forget the times that security is intentionally misconfigured so certain individuals are permitted a backdoor access.

Security, just like performance tuning, is a perpetual (and sometimes iterative) task. There is no excuse for setting your security and forgetting it. It must be routinely reviewed.

While performing a script review for a client, I was reminded of the need to also review their security setup. The reminder was more of a slap in the face as I noticed that the developer had built in some permissions assignments for some upgrade scripts. Unfortunately, we were not permitted to alter any of the scripts due to them being from a third party vendor (and that vendor refused as well to fix the problems with the scripts but I digress).

What could be wrong with this?

I want you to digest that for just a moment. This is an example of the permissions this particular vendor insists on setting for the public role. What could possibly be wrong with that? Let’s examine a couple of the permissions like “Control” and “View Change Tracking”.

View Change Tracking

This permission is an elevated permission that is required in order to use the change tracking functions. This permission is necessary for the following reasons:

  1. Change tracking records contain the PK value for rows that have been deleted. If sensitive information was deleted that a user should not be able to access, the user would be able to reverse engineer the data from the change tracking data.
  2. A user may be denied access to a column that contains sensitive data. If data in the column is changed, the data would be stored in the change tracking and a user can determine the values that were updated for the sensitive data.

Control

I am going to take this one direct from the Microsoft documentation.

Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.

Now digest that a bit. Once digested, consider what the public role does to user access in a database. The public role permissions are inherited by all users of the database whether the users have been granted the permission or not. You should only grant permissions to the public role that you really honestly believe that ALL users should have. If you are being serious in your role, then the amount of times you grant permissions to the public role should either be a) never, b) when you want to have a data breach, or c) you are testing in a sandbox to improve your skills.

Check for Perms

When you are uncertain of which permissions have been assigned to the public role, or you just haven’t reviewed your permissions real-estate in some time, it is best to pull out a script and start the process. As luck would have it, I have a few scripts that can help with that (here or here) and I have a new one that I am sharing now.

Let’s start with a basic query that will display all of the permissions assigned to the public role in a specific database.

There is nothing super special about this query. Looking at it, it is querying the permissions for the public role specifically. I display where the permission is a “Deny” or “Grant”. Then we list the permission name and then the schema and the object.

Let’s take that script and evolve it now. I am going to plan for the worst and expect that some permissions have been applied that shouldn’t have by some vendor upgrade script (because – well, history). Since I am expecting the worst, I am going to add some script generating code that will revoke the unwanted permissions. And still expecting the worst would be that revoking the permissions will break something, I will also add some code that can generate the appropriate “Grant” statements.

That looks better. I have a way of identifying the unwanted permissions as well as an easy script I can execute to remove the unwanted permissions. Note the use of the collate in the final two columns. As it turns out, permission_name from sys.database_permissions has a column collation of Latin1_General_CI_AS_KS_WS. Since I ran into some errors (shown below), it is easier to direct the DB engine to use the collation that matches the permission_name column.

Msg 451, Level 16, State 1, Line 11
Cannot resolve collation conflict between “SQL_Latin1_General_CP850_CS_AS” and “Latin1_General_CI_AS_KS_WS” in add operator occurring in SELECT statement column 5.
Msg 451, Level 16, State 1, Line 11
Cannot resolve collation conflict between “SQL_Latin1_General_CP850_CS_AS” and “Latin1_General_CI_AS_KS_WS” in add operator occurring in SELECT statement column 6.

Alas, this is still not quite as efficient of a script as I would like. I may have hundreds of databases on the instance and need to evaluate all of them. Time for the bigger guns.

That will take care of all of the permissions for the public role in all of the databases, with a slight caveat. I am only checking against that objects that are not flagged as is_ms_shipped. Now, isn’t there also a public role at the server scope? Indeed there is! Let’s also capture those permissions.

Now, I feel many times better about what could possibly be going wrong with the public role.

If you are in a tightly controlled environment or you are just sick of people doing this sort of thing to your servers, there are more extreme measures that can be taken. You can read about it here or here.

The Wrap

It is amazing what some people will do that just doesn’t make sense. Granting permissions to the public role is one of these cases. That behavior also explains why there are documents and procedures for hardening the public role (here and here).

If necessary, I recommend locking down your public role. It will make your job a little easier and give you better rest at night.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Easy Permissions Audit

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: January 21, 2019

Something I have written about more than a handful of times is the need to audit. When people think about audits, the first thing that comes to mind is most likely the IRS and taxes. More than taxes are audit-able. Despite that tendency to first think taxes when somebody says “audit”, I am not writing about taxes. I will typically write about the different topics within SQL Server that can be audited. Has Johnny performed a logon to the server? When was the last time the permissions to the database changed? Did somebody change an object? When was the last time stats were updated? How about auditing success and failure of your backups (or all agent jobs for that matter)? Those are the topics I will typically write about. Today, I am going to share a simple method to help perform an easy permissions audit permissions – on a manual basis.

Easy Permissions Audit

As the article title denotes, today I will be discussing a simple way to get quick permissions for various principals. If you are looking for a more comprehensive and human friendly report version, I recommend reading any of my many other articles on the topic such as the following article – here or here. In the second of those links there is a clue as to what tool we will be using in this easy version of the audit. That tool is called sp_helprotect.

The stored procedure sp_helprotect is a system stored procedure from Microsoft that can help divulge permissions for various principals in a simple table result set for you. Bearing in mind that I am keeping this to a simple audit, the examples will be simplistic in nature. Without further ado, here is the easy audit for your permissions.

sp_helprotect

This stored procedure was introduced in SQL Server 2008 and comes with a few parameters to help narrow the results down to a specific principal and even to any object to which that principal may have been granted permissions. Here are those parameters for quick review:

@name = This parameter is to filter your request down to a specific object or a statement that can be executed against that object (e.g. alter, create, drop)

@username = Is the name of the principal for which permissions are returned.

@grantorname = Is the name of the principal that granted permissions.

@permissionarea = This is the group of grant-able permissions. There are two types of groups: object and statement. The default setting here is to return both groups.

The easiest way to use sp_helprotect is as follows:

Do you see how easy that is? This returns the following results for me.

Note from the results that I see results for roles and users for various different objects. This is due to how the procedure was executed – with no parameters. Using no parameters in this query, the default behavior is to return as much information as possible for all objects and principals within the database.

What if I only want the results for a principal named “Gargouille”? I can do that in the following way.

Now, I will receive the following results:

Recap

There are many ways to produce an audit. Today, I have shown how one can produce a permissions audit when in a hurry that will produce a simple result set for database permissions. I want to underscore that this was at the database level and not the server level. While this is an adequate means for a quick peek into some of the objects and granted permissions, I do recommend using one of the other methods I have introduced in the other articles for ongoing complex audits and results that are somewhat prettier and more human friendly to read.

For more articles on audits and auditing check here and here.

Quick Permissions Audit

Whether it is for a client, an audit, or just for good housekeeping, DBAs will often need to figure out who has access to what.  In addition, they may need to know by what means people have access within your databases.

When that need arises, it is frequently adequate to just perform a quick audit and then dive deeper if the quick audit shows anything questionable. Today, I am sharing a means to perform this quick audit. I will start with the logins (server principals), peek at the users (database principals), then the server roles, and wrapping it up with the database roles. What I don’t do is dive into the windows group memberships.  So if a windows group exists within SQL Server as a login, I do not try to figure out who is a member of that role.  That can be obtained, but is a separate discussion.

So, in the vein of a quick permissions audit, I will quickly get to the script to help perform this permissions audit.

As you look through this code, you will notice that I have done a few interesting things.  Here is a quick recap:

  • Build a temp table based on a delimited list of databases input into a variable
  • Build a temp table based on a delimited list of logins input into a variable
  • Used a string splitter known as DelimitedSplit8K by Jeff Moden (google it) or LMGTFY
  • Used FOR XML to build a delimited string of Server Role Permissions
  • Used FOR XML to build a delimited string of Database Role Permissions
  • Used a CURSOR to build a dynamic SQL string to execute for each database

Holy cow! That is a lot of stuff for such a simple quick audit.  A cursor? Seriously? Well yeah! It is OK in this type of scenario to use a cursor.  This is a well-performing cursor for an administrative function. In addition, I chose to use sub-queries to build my delimited string of permissions so I could keep those things together in a single record per login/role.

If I run that query on my instance of SQL Server, I would end up with something that looks like the following.

Now, obviously, unless you had these same databases, logins, and even the same permissions assigned to these logins, your results will be different. So, make sure you go and change those inputs for logins and databases to be queried.

For more articles on audits and auditing check here and here.

Audit Domain Group and User Permissions

Comments: 3 Comments
Published on: December 29, 2017

No matter how simple the task or how versed we are with doing a security audit, it seems like we can always stand to learn just a little bit more. No matter how many times we hand an audit report over to the auditor, there is always “just one” more report we have to provide.

I know it seems like I am almost always running some sort of audit report. Whether it is to audit which user may have changed something or what access a user may have overall to the system, there is always a need for more audit.

One of the aspects of an audit that I have not yet written about is with regards to Domain Groups. It is a very common practice to grant access to SQL Server via Domain Group membership. We can easily see which groups have which access, but how often do we try to figure out who is in the group?

When you do try to figure out who is in a Domain Group, do you do like so many DBAs that I know and yell across a cubicle wall to the AD guys and ask them to look it up? Maybe you fire off an email to the AD Admins and ask them to do the grunt work for you. Nobody will shame you for doing either of those two methods. I mean, most people probably toss it over the fence in one way or another.

Fence

 

It is not a difficult stretch to imagine asking somebody who is an expert at a technology to see if they could get a quick answer to your question for you. That said, it is also really nice when you can service those types of questions by yourself and thus save everybody a little time and effort. Luckily, there is at least one easy way for the SQL Server professional to try and answer questions about group membership.

Microsoft has included a method for the DBA to try and capture information about AD related accounts. This method comes in the form of an extended procedure called xp_logininfo. This procedure can tell me the members of domain groups, the access level the account has, and also validate if the account is still active in AD.

Suppose you needed to determine all of the accounts that may have access to SQL Server, via direct Login or via group membership. In addition, you must find out which of the accounts are no longer valid in AD. I have a script that will do just that by first perusing the groups and then cycling through each of the User accounts produced from the group perusal.

Let’s take a look at that script.

You will see here that I have a couple of cursors built into this script. This is helping me cycle through first the groups and then the users. As I cycle through each user, I am able to determine the validity of the account. Not only will I know if an AD account is dead and needs to be removed from SQL Server, but I will also know if a Domain Group no longer exists.

This little script has proven to be a major time saver with some clients. Not only has it saved me some time, but it has also helped to figure out how a developer was able to perform sysadmin functions on a prod box even though his account did not have sysadmin access granted explicitly.

Extended Events Permissions

Comments: 1 Comment
Published on: December 25, 2017

A common question that I am asked about Extended Events relates to what kind of permissions are required in order to use this awesome feature. In this article, I am going to compare and contrast the permissions required for Profiler as well as Extended Events.

Extended Events changed a lot with how to monitor your server. One of those changes is a slightly different set of permissions to be able to perform various different tasks. Before I dive into what the security landscape is with XEvents, let’s take a peek at what we have to do in order to be able to use Profiler.

Profiler

If you try to launch a profiler session as a standard login with what should be a typical user type account with restricted access, you will encounter something like the following.

Yes, according to the message you need to be a sysadmin (nobody reads past that first part so they always miss the alternative). So, let’s test things out. I will create a login called TraceUser and attempt to do tasks related to running a Profiler or Server Side type of trace. I will also use this same login when testing the permissions for Extended Events.

I included a quick test script after creating the login to confirm that the user does indeed have restricted access. The test script will produce the following message.

Msg 8189, Level 14, State 6, Line 8
You do not have permission to run ‘SYS.TRACES’.

This is the same net result as if I were to attempt to run a Profiler session – it’s just faster to get to the result. As we can see, the TraceUser login does not have the requisite permission to do anything with traces (profiler or server side).

Now, I will take this just a step further and try to add permissions to validate the minimum permissions needed.

Running this test script now will yield a result of all traces that are currently setup on the server. So, in my case that would be just a result showing the Default Trace and nothing else since I have not put any other traces on the server. I can also confirm the exact permissions granted to TraceUser do not actually exceed what I just granted.

Or is it?

After running that in the master database I will see that granting the “Alter Trace” permission has an extra side effect of granting “Showplan” to the user.

XEvents

For Extended Events, we have something a little different and a little more (just a little) granular.

Wow! That is quite the permissions escalation, right? Well, it’s not significantly different in some regards to what we see with Profiler. Taking it a step further, XEvents is more in tune with a lot more internals for SQL Server than Profiler. Little things like looking at DMVs or getting the current status for things like Hekaton or Availability Groups, which tie deeper into the engine, will require more permissions. That said, view server state or even these permissions levels is not the end of the world. There are work arounds to the permissions issues. Before I give the work arounds, lets look at the permissions.

Using the TraceUser login I created earlier in this article, I am going to start testing XEvent permissions.

The first step I took in this script was to revoke the “Alter Trace” permission. Then I queried both sys.traces and sys.dm_xe_objects along with running my permissions checks. The attempt to query the two views produces the following messages.

Msg 8189, Level 14, State 6, Line 34
You do not have permission to run ‘SYS.TRACES’.
Msg 300, Level 14, State 1, Line 36
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 36
The user does not have permission to perform this action.

From the permissions checks, I can also confirm that this login has barely the ability to connect to SQL Server.

I will now grant permissions little by little to see what happens.

Here is the sum of the differences.

The “Alter ..Event Session” permissions should be expected since that is what I explicitly added. That said, the permission, while enough to create an event session does not yield enough permissions to do anything with viewing the session data or looking into the dmvs or catalog views. In fact, if you have a recent enough version of SSMS, the XE Profiler feature will cause SSMS to crash if you try to “launch” a session via the Profiler GUI with only the “Alter Any Event” permission.

That said, I can run the following script to create a new Event Session.

As we can see, the permissions are adequate to create a session via TSQL and it does not cause SSMS to crash. This would be another consideration if using the XE Profiler (and even if you are going to allow non DBAs to create XE Traces) in addition to what I noted recently in this article – here.  Truth be told, the traditional GUI for XE also will not allow users to access the Extended Events node if they have “Alter Any Event” or lower permissions – you must use TSQL to alter sessions. The caveat being that only the XE Profiler feature causes SSMS to crash (I tried it 6 times in a row with consistent results).

So, if “Alter Any Event” is only going to get us partially there, let’s go ahead and grant the view server state permission.

Due to the nature of sys.traces, I still do not have permissions to view that data. So that is a good thing. On the other hand, I can now query all of my XE related views (you can read more about those in my 60 day series). And I now can see that I have the following permissions.

And now I can do all sorts of fun stuff from the XE GUI.

That basically lays out the required permissions for both Profiler and Extended Events. In either case, I view both of these as tools for the trusted DBA. Outside of the DBA team, there are select other individuals who may think they need the access and then there are others who really could use the access to the tools but should not really be granted full sysadmin access. At times, you may not even want those individuals to have anywhere near “view server state” access because there is a chance that individual could see something they are not permitted to see. What do we do with that?

Work Arounds

This is when we figure out a way to circumvent the restrictions. Don’t view this as a hack to the system or a way to violate trust. Rather these are methods to help think outside the box and work together as a cohesive team.

Method 1 is a solid solution. That said, it does have a high management overhead. Aaron Bertrand has offered a solution and it is a secure solution. Aaron has found a way to work with Trustworthy databases, views, and logins to help provide a workable solution while still securing the event session data. You can read his article here.

Method 2 is also a secure solution and does require a bit more communication but less management and upkeep than method 1. I like to save the event data to file. Working with the person that would like to be able to review the data, I can create an appropriate XE Session that will be highly unlikely to capture anything they should not see. In addition, I do not need to grant that person any elevated permission on the production server. Once the trace is complete, I can copy the event files to a dev server where that person has elevated access. Then they can merge the XEL files (I show how to do that in the 60 day series) and review the data at their leisure.

Method 3 is similar to the previous method but it requires more work similarly to method 1. This last method, I can grant the user specific access to read data from a specific table in a specific database. I then setup an automated process to dump the data into that table or I can manually load it for the user. Again, the permissions here are very limited in scope and the person has nothing more than the ability to Select from a pre-defined table in a pre-defined database.

I don’t see the permissions for Extended Events as a limitation, rather they seem appropriate based on what XE does and the extensive nature of the tool. I recommend you work with XE for a bit and see what you can come up with to help improve your efficiency and the efficiency of our team.

 

 

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.

Column Level Permissions

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: September 19, 2011

Did you know that you can grant permissions down to the column level in SQL Server?   Well, if you didn’t know that – you do now.

It is actually rather simple to grant permissions at the column level.  This can be demonstrated by the following script.

[codesyntax lang=”tsql”]

[/codesyntax]

If you want to check out more on that syntax, read here.

And then…

Why is it important to know that you can do this?  Well, it is quite possible you have some of these permissions already in place.  It is possible you may have inherited something like this.  Just maybe there is a business requirement requiring that certain users or groups only have access to certain data within certain columns.

That brings up a new problem then.  How do you find out what columns have specific permissions applied to certain users?  Well, that is actually pretty straight forward.  We can query the system views and determine column level permissions.

[codesyntax lang=”tsql”]

[/codesyntax]

The previous query is a really simple version of how to find this information.  As you can see, I am simply returning the UserName, TableName and ColumnName along with the permission in effect on that column.

You should also be able to see that the mapping between these system views is pretty straight forward as well.  Major_id maps to object_id and column_id maps to minor_id.

Conclusion

This query can be of good use to determine permissions in place for columns in tables within your database.  Furthermore, you can even use this query to simply test your curiosity as you check to determine what has been put into effect in the databases you manage.

There are more complex methods to determine these permissions.  With there being more complex methods, I am sure there are also some easier methods.  Let me know what you do to query these permissions.

CRM Data Source Connection Error

Working through some security modernization recently with a client I ran into a fun little nugget. What we were trying to accomplish was to make the service accounts being used more secure through the use of Managed Service Accounts.
Making the change was fairly easy and all of our services seemed to start up swimmingly. SQL Server was running perfectly fine. Even when we got to SSRS and applied the change to the Service account via SSRS Configuration Manager, things went smooth.

After ensuring the services all started came the task of validation. From a sysadmin perspective we often figure the validation process means looking through the tools we know and sometimes we don’t know what to do via the application to validate functionality. For this we more or less rely on the end-users. While waiting for the end-users to perform their validations, we resort to the tools known best to us – SSMS etc.

In the case of testing reports, one may attempt to use the Report Manager url (or report server url). When opening reports, you may see that everything is working correctly through that tool. So, you continue to wait for the end-user to validate. Eventually they say it all looks good only to report back a week later that nothing is working and now you are stuck trying to figure out what to do to resolve the problem.

SSRS Service Account Needs PrivUserGroup

This is particularly interesting given everything worked just fine before the service account change and given that the only thing that changed was the service account (via Reporting Services Configuration Manager). Nothing changed with the execution account (an execution account was not actually employed in this configuration at any point).

We also determined that nothing was necessary to be changed with the CRM’s “SRS Data Connector”. What is the next best thing? Well, time to take a look into the logs and try to find some clues there. Perusing the logs, I was able to find a couple of key indicators. Here are the two enlightening errors.

First error:

“Cannot create a connection to data source ‘CRM’.”

Better, more verbose exception that broke the case wide open:

“Immediate caller <SQL Server Reporting Services Service Account> has insufficient privilege to run report as user <SID>”

Googling for these errors revealed a lot of information about the errors in the event an Execution Account for SSRS was being used. That scenario didn’t apply but it did give a hint at what to look at next. Comparing the previous service accounts permissions in Active Directory to the new service accounts permissions we found the “PrivUserGroup” permission was missing. This permission was the exact permission mentioned in all of the other articles. Adding that permission immediately solved the issue for the end-users and their ability to run reports via the CRM application.

The Wrap

This article takes us to the edge with a couple of CRM related errors after changing the service account to a more secure Managed Service Account. Despite the CRM reports working properly within Report Manager (via SSRS), the reports would fail in CRM.

Comparing the permissions for the old service account versus the new service account shed a bright light on the problem indicating a missing permission. PrivUserGroup is essential to proper report functionality and should be added to either your execution account or your SSRS service account.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Cannot Open Backup Device

Your success as a DBA is directly relational to your ability to ensure proper Database backups are occurring. There is a whole slew of considerations that go into effect when discussing what a proper Database backup actually is. To drill it down to its most basic definition, a proper database backup is one that fulfills business requirements for recovery, retention, and has been routinely tested to ensure the backup can be restored.

Remember, a backup that cannot be restored is factually not a backup. Why? Well, going back to the definition of a backup, it is a fail-safe mechanism to be implemented in the event of a failure. If it cannot be implemented (in this case restored), then it provides no value because it cannot be used.

What about when you run into a problem even getting to the point of getting a successful backup operation? For instance, this error can be a bit troubling and may just cause you to lose plenty of time troubleshooting it.

Msg 3201, Level 16, State 1, Line 3
Cannot open backup device ‘\\SomeServer\C$\MSSQL14\MSSQL\DATA\Full_Backup2.bak’. Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

Sure, the error seems to report plenty of adequate information, but you may be surprised at the pain this particular error can cause.

Backup Device Permissions

If you search long enough you will come across numerous articles or forum posts that all pretty much have the same direction. They all point to the problem being a permissions issue. There are a few concerns with the permissions stigma however. In a recent issue for a client, we could verify that permissions were indeed not at fault. To ensure the permissions were not at fault, we granted local admin in the OS as well as sysadmin within SQL Server for the service account. In addition, we also tried a proxy account with full blown access in Windows and in SQL.

To take it a step further, we also ensured that the special permissions for service accounts were also in effect. Those permissions are:

  1. Permission to bypass traverse checking (SeChangeNotifyPrivilege)
  2. Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
  3. Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
  4. Permission to access this computer from the network (SeNetworkLogonRight)

None of this seemed to have any effect when trying to perform a database backup in SQLServer. In our case, we also ensured that the service account had full blown access to the folders as well.

Following the traditional troubleshooting measures, I created a virtual backup device as well only to get the same error. I tried to use the UNC from a remote server and it worked perfectly fine for all accounts in question. What could be going on? Then, I figured why not try the UNC from the server itself. Finally, a clue! Suddenly I was getting prompted for my credentials but my credentials would not work no matter what. Just for giggles, I verified my account was not locked out, and indeed my account was working just fine.

As it turns out, sometimes the problem is not really a permissions issue but it is a veiled security issue. You see, if I use the actual server name instead of the CNAME in all cases above, suddenly everything worked. The issue turns out to be more along the lines of a Windows issue and is documented here (still valid on Windows 2016).

Here is the basic SQL backup test script that led us down the path to the CNAME being the issue.

Put a bow on it

When running into error 1326, it makes plenty of sense to try to create a backup dump device (only as a testing exercise) as well as test the connectivity to the UNC path from the local server instead of a remote server. In my case, the problem was the local server was blocking access to a CName version of the server.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the eighth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

«page 1 of 6

Calendar
February 2020
M T W T F S S
« Jan    
 12
3456789
10111213141516
17181920212223
242526272829  

Welcome , today is Wednesday, February 26, 2020