Security as a Fleeting Thought

Comments: 6 Comments
Published on: February 10, 2015

Today we have another installment in what is known as TSQL Tuesday.  This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter).

TSQL2sDay150x150Today, the invitation is for us to share our stories on how we like to manage security.  Or at least that is the request that was made by Kenneth.  I am going to take a bit of a twist on that request.  Instead of sharing how I like to manage security, I am going to share some interesting stories on how I have seen security managed.

Let’s just call this a short series on various case studies in how to manage your security in a very peculiar way.  Or as the blog title suggests, how to manage your security as an afterthought.

Case Study #1

dbsecurityWe have all dealt with the vendor that insists on the user account that will be used for their database and application be one of two things.  Either it needs to be sa or needs to be a member of the sysadmin fixed server role.  The ensuing discussion with those vendors is always a gem.  They insist the application will break, you as the diligent DBA prove otherwise, and then the senior manager sponsoring the application comes around with a mandate that you must provide the access the vendor is requesting.

Those are particularly fun times.  Sometimes, there is a mutual agreement in the middle on what security can be used and sometimes the DBA just loses.

But what about when it is not a vendor application that mandates such relaxed security for their application and database?  What if it happens to be the development group?  What if it happens to be a developer driven shop and you are the consultant coming in to help get things in order?

I have had the distinct pleasure of working in all of those scenarios.  My favorite was a client that hosted ~700 clients, each with their own database.  There were several thousand connections coming into the server and every single connection was coming in as ‘sa’.  Yes, that is correct.  There were no user logins other than the domain admins group on the server – which was also added to the sysadmin security role.  That is always a fun discussion to start and finish.  The look of color disappearing from the clients’ eyes as the realize the severity of the problem.

Please do not attempt this stunt at home.

Case Study #2

In a similar vain, another one that I have seen far too often is the desire to grant users dbo access within a database.  While this is less heinous than granting everybody sysadmin access – it is only a tad better.  Think about it in this way – does Joe from financing really need to be able to create and drop tables within the accounting database?  Does Marie from human resources need to be able to create or drop stored procedures from the HR database?  The answer to both should be ‘NO’.

In another environment, I was given the opportunity to perform a security audit.  Upon looking over things, it became very clear what the security was.  Somebody felt it necessary to add [Domain Users] to the dbo role on every database.  Yes, you read that correctly.  In addition to that, the same [Domain Users] group was added to the sysadmin server fixed security role.  HOLY COW!

In this particular case, they were constantly trying to figure out why permissions and objects were changing for all sorts of things within the database environment.  The answer was easy.  The fix is also easy – but not terribly easy to accept.

Please do not attempt this stunt at home.

Case Study #3

I have encountered vendor after vendor that has always insisted that they MUST have local admin and sysadmin rights on the box and instance (respectively).  For many this is a grey area because of the contracts derived between the client and the vendor.

For me, I have to ask why they need that level of access.  Does the vendor really need to be able to backup your databases and investigate system performance on your server?  Does that vendor need, or are they even engaged, to troubleshoot your system as a whole?  Or, do they just randomly sign in and apply application updates without your knowledge or perform other “routine” tasks unknown to you?

I have seen vendors change permissions and add back door accounts far too often.  They seldom if ever are capable of providing the level of support necessary when you are stuck with deadlocks by the second or blocking chains that tie up the entire server.  In addition, they are generally unavailable for immediate support when a production halting issue arises in their application – or at least not for a few hours.

This is specifically in regards to application vendors.  They are not your sysadmin and they are not your DBA.  If they must have RDP access or access to the database – put it under tight control.  Disable the account until they request access.  Then a request can be made and a note documented about why the access is needed.  Then the account can be enabled, monitored and disabled after a specified amount of time.

Please do not attempt this stunt at home.

This also changes when that vendor happens to be providing you IT functionality and is not specifically tied to an application.  Those relationships are a bit different and do require a little more trust to the person who is acting on your behalf as your IT staff.


I have shared three very dangerous stunts that are sometimes portrayed to be done by professionals.  Do not try this in your environment or at home.  It is dangerous to treat security with so little concern.  Security is not some stunt, and should be treated with a little more care and attention.

If you find yourself in any of these situations, an audit is your friend.  Create some audit process within SQL Server or on the Local server to track changes and accesses.  Find out what is going on and be prepared to act while you build your case and a plan for implementing tighter security.

T-SQL Tuesday #58 – Security Phrases

Comments: 2 Comments
Published on: September 9, 2014

TSQL2sDay150x150Today is once again TSQL Tuesday.  This month the event and topic are being hosted by Sebastian Meine (blog | twitter).  You can read all about the topic this month on his blog inviting all to participate.

Despite Sebastian being a real cool kid, I was not too hip to the topic this month.  Not because it is a bad topic or anything, it’s just that I really had nothing that seemed to stand out as easy to write for the blog party.

Then all of a sudden, a nice fat, juicy pork chop landed right in my lap.

The Pork Chop

A client requested that I make some changes to a task on a development server for them.  As it happens, the task is a powershell script that was being run on a schedule via a Scheduled Task in the Windows “Scheduled Tasks” control panel.  Making the requested change is a no-brainer of a change – or it should have been.

The change was to change the owner/executor of the scheduled task to the service account for the SQL Service.  By doing that, they would be less likely for the job to fail in the future due to an employee leaving the company.

As luck would have it the client DBA happened to know the password for the service account.  When changing the task to use the service account with the supplied password, we soon discovered that the supplied password caused the service account to become locked.  OUCH!

Maybe it was just fat fingered?  Nope, no dice!  As it turns out the DBA had the incorrect password and did not know the correct password.  Worse, nobody else knew what the correct password was.  Due to this issue, I proposed that the sysadmins and I work together to get the password changed.  That is to be done at a future date.

In addition to this, we decided that the passwords need to be more accurately documented.  These should be stored in an encrypted vault (the application is your choice).  But the mere use of an encrypted vault is far better than the use of a sticky note to document passwords (and I have seen that far too often at client sites).

This is just a short and sweet post for the day.  I think that it demonstrates problems that can arise from bad password management and also the risks that could come from that password management.  In our case, it was at least a Dev server with minimal users.

Slammer, Alive…Barely

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 1 Comment
Published on: January 24, 2012


By now you must have heard of the SQL Slammer worm.  It was quite an infectious little nuisance.  The harm it caused came largely due to unpatched, unprotected SQL Servers.

We are now 9 years out from the initial discovery of this worm.  The worm has made its way onto the endangered species list – but it is not yet extinct.  I don’t know if I should be surprised by that.

My initial reaction is “No way that worm is still causing problems.  Everybody knows about it.”  But yet, I just caught several infection attempts from remote hosts that were affected by Slammer.  When I take a step back, I recall that many people out there are still running on unpatched servers.  I know of many places that are running SQL 2000.  I know of a large pool of servers across different versions and editions that are not patched.  I even know of a few places that are still running SQL 6.5.

When I take all of that into account, finding that Slammer is still active does not surprise me – but it should.

So for fun, here is what I was able to trap from the recent attempts at my machine with SQL Slammer.

When I trace that IP back to its source, I get a host name of the machine.  If I search on the Host Name of the IP Address, I find this page.  If I were a hacker, I now have a lot of valuable information.  I can also assume that this particular host has many virii.

This entire little foray has made me wonder how many people out there are concerned about security.  Do you know what the patch level is of your server?  Is your AV software up to date?  Are you running any form of HIPS?  If you are in IT and your focus is Data, you may want to check those things.  After all, our focus is to protect the data.

A Trio of Functions

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: January 17, 2012

I found myself perusing an execution plan the other day.  I know, big surprise there.  This execution plan showed me some interesting things I had never really paid much attention to in the past.  When I started paying attention to these things, I found myself jumping down a rabbit hole.

It all started with a bit of curiosity to see if I could make an “admin” script perform a bit better.  The execution plans started showing some table valued functions that I knew I hadn’t included in the query.  Subsequently, I found myself wondering – what is that?

The items that made me curious were all table valued functions.  There were three of them (different) in this particular plan.  I started looking hither and thither to find these functions.  It didn’t take long to figure out that I could find them in the mssqlsystemresource database.  So I proceeded to making a copy of the database and attaching a copy of it for further learning opportunities.

The three functions are:




Knowing the query and based on these names, I began looking in the appropriate DMOs to see what I could find.  Here are the scripts for each of those DMO’s.

[codesyntax lang=”tsql”]


Cool.  I can now see the internals of each of the DMOs – sort of.  You see, there is an OPENROWSET call in each of these objects.  Each call uses an undocumented feature called TABLE.  This is an internal command used by the engine and you won’t find much on it (mostly people asking what it is and Microsoft saying they won’t tell).

Here is the fun part.  If you try to run that code outside of querying the DMO, you will receive error messages.  If you try to create a new view utilizing the Openrowset, it will fail.  It is reserved for internal usage.  With that said, just continue to use the DMO and you will be fine.  Personally, I was curious to find out how it worked so I tried a bit to find it.

So there you have it.  If you are curious what is the internal makings of these DMOs, you can script them from the resource database.  Alternatively, you could also run sp_helptext.  I like to check these things from the resource database.  It feels more like an adventure.  Have fun with it and see what you will learn.

Public Role and Security

Tags: ,
Comments: 2 Comments
Published on: December 20, 2011

Having flown a fair amount lately, I was thinking about the various levels of security within an airport.  Part of that comes from seeing signs like the following all over the place.


These signs are placed in various places for very good reason.  But seeing a sign such as this made me wonder about the various access levels in an airport and how they might relate to the database world.

Let’s start with some of the zones that might be recognizable in an airport.  First there is the ticketing counter.  This is a general access area open to the public.  Anybody can approach a ticketing counter whether they are intending to purchase a ticket or not.  Another area similar to this is frequently the baggage claim area.  These are common areas and generally less secure than other areas.

Next, you might encounter the concourses.  Only ticketed passengers and authorized airport personnel may enter these areas after some degree of screening.  From these areas you have greater access to the airplanes.  You have been explicitly granted permission to enter an aircraft and are required to have a separate pass for each craft you desire to board.  If I were to correlate this to security in SQL server, this would most closely match the db_datareader database role – for which explicit permission has to be granted for the user in order to access each additional database.

From this same area you may witness that there are several aircraft crews.  Each member filling a specific role.  There is a pilot, copilot, flight attendants and ground crew.  Each role may have different access throughout the airport.  And in the case of the flight crew, they have to be given access to each plane they will board.  A United Airlines flight crew cannot go and pilot a British Airways craft for instance – they are responsible for specific flights belonging to UA.

Another potential role is that of the control tower.  The personnel manning the tower have access to quite a bit more than a pilot or passenger.  They have access to communications between all flights and the ground within their airspace.  They are coordinating efforts and trying to make the whole thing go smoothly.  These guys are much more like the specialized server roles in SQL Server.  They can be passengers and have public access.  They can also assist in the piloting of a craft (if you believe what you see in the movies) while giving instruction for flight path, landing and takeoff.

The point is, there is highly segregated roles in an Airport and in the air when an aircraft is involved.  The same should be true in a database environment.  There are special server roles that include public, sysadmin, securityadmin, and diskadmin (amongst others).  Then there are specific database roles that come prepackaged as well as the ability to create any number of specific roles that you need to run your environment.

Now let’s step back out again to the airport example and the public access areas.  These are the least secure areas.  Also, there is a group of people that we should call public.  I am a part of this particular group.  Every person that enters an airport is a member of this group.  The crew piloting a craft is a member of this public group, but they are also members of other more restricted groups.

Being a member of just the public group does not get me permission to enter the pilots cabin.  It does not grant me permission to enter the flight control tower.  It does not even grant me permission to stand behind the ticket counter.  You wouldn’t want just any old Joe Schmoe entering those particular areas – so they become more secure.  And the public group is denied access.

Back to the public server role in SQL Server.  This role is granted VIEW Any Database as well as Connect, but by default is limited to just those permissions.  Can that be changed?  Sure – just like I could walk behind the ticket counter or walk into a pilots cabin (the flight staff may occasionally allow you to take a peek – typically children though).

Just because it can be changed – doesn’t mean it should be done.  In the example of me taking a peek into the Pilots cabin, that is a one person permission being granted.  If I granted that permission to the public role in SQL Server, now everybody can do that same thing.  So think about it for a minute, do you really want everybody being able to change the schema in your database if you decide to grant alter any to public?  I really doubt it.

A good rule of thumb with the public role is to leave it be.  Do not add permissions to this role.  Add permissions on a per database  and per group of users basis.  Create roles within the database and grant permissions to that role – in each database.  And remember the rule of least privilege – don’t grant more permissions to a user/role than necessary to perform the job function.  Just the same as in an airport – everybody has their role and it is strictly defined.  If the user need not have access – then don’t grant the permissions.

I want to re-iterate that point.  To help prevent unauthorized access, keep permissions in the public role to a minimum and create roles within the database to manage the different job functions as necessary/possible.

Stored Procedures – Common Security Practice

Tags: , ,
Comments: 1 Comment
Published on: September 20, 2011

In SQL Server a good practice is to access the data via calls through stored procedure.  Have a look at the document available in that link.

To further this practice, one may create a database role, then add users to that role.  Permissions to execute the stored procedures would then be granted to the role.  A role is simple enough to create.  You can do that with the following code.

[codesyntax lang=”tsql”]


After creating this role, simply add users to that role.  The next part of the process is to ensure that you have granted the appropriate permissions to this role.  There are two methods to do that: 1) blanket execute to all procedures, and 2) pick and choose the procs to which you wish to grant permission.

Before we get to adding permissions, let’s create a little test proc for testing purposes.  I will reuse something from a past article to simplify.  In that article, I already did the setup for the table – you can get it from here.  The stored procedure is as follows.

[codesyntax lang=”tsql”]


Let’s also make sure that the user has been added to the role.

[codesyntax lang=”tsql”]


As for the exercise in how to create that database user and the associated login, I will leave that for you to do.

In order to test, we need to connect to the SQL Server as that user.  Once connected, run the following to verify that your session is connected as desired.

[codesyntax lang=”tsql”]


On my connection, running that query will show that the LoggedInUser is testu.  Now, having confirmed that I am connected as the appropriate user, I will try to execute that test proc we created.

[codesyntax lang=”tsql”]


At this point, the expected results should be similar to this error message.

[codesyntax lang=”tsql”]


Now, I will switch over to the previous connection where I have administrative permissions.  I will now proceed to grant execute permissions following the first method – blanket grant.

[codesyntax lang=”tsql”]


And now, flip back to the user connection to test our permissions.  Try running that proc again, and your results should be similar to these.

ColorID ColorPlate ColorType
1 Red 1
2 Blue 2

That is good, but what else can this user now do?  The user can execute all user created stored procedures.  Do you necessarily want this?  What if your business requirements specify that certain user groups be able to execute only certain procs?

In that case, we now need to grant execute permissions on a more granular level.  A big problem with this method pops up right from the beginning.  What if there are thousands of stored procedures?  What if you need to grant execute permissions to hundreds of stored procedures for each role?

We have two avenues for these types of situations.  One avenue is to separate the various stored procedures via schema and then grant execute to the schema.  The other is via naming convention.

In the event you have a suitable naming convention to help mass assign permissions, here is a little script to help.

[codesyntax lang=”tsql”]


It is very simplistic, I know.  I also left an example of such a naming scheme.  In this example, the naming convention may imply that the procedure is a Reporting Services stored procedure.  I can query for all of the Reporting Services procs in the database, and then assign permissions to all of them much faster.

This method does not immediately grant permissions to the entire result set.  It does allow for you to review the results.

Are there more elaborate examples out there?  Certainly.  Find a method that suits you.  I would be very careful about using the first method though – it just might be too much granted to the user.

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”]


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”]


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.


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.

T-SQL Tuesday #17 – APPLY Knowledge

Comments: 3 Comments
Published on: April 12, 2011

We have another opportunity to write as a part of TSQL Tuesday today.  This month Matt Velic (Blog | Twitter).  Matt has proposed a challenge that was derived from a comment on twitter.  The challenge this month is to write about “Apply.”

Admins Apply Within

As an administrator of databases, do you use Apply?  Not only can Apply be used to help return result sets as a part of your application, it can come in quite handy from an administration point of view too.  It can be used to help retrieve the text of currently executing code or code stored in cache.  Apply can be used to help retrieve index information and it can come in quite handy when performing a security audit.

Some Back Story

Just a few days ago, I posted some scripts to help find role membership of logins at the server level.  I wasn’t satisfied with the query and decided to rewrite it as a part of my entry for this months blog party.  You can read up on that script here.  My dissatisfaction with the query was that it felt klugy.  I wanted to write the query with something other than the Union and to present fewer rows to user.  So, I took another look at the query and purpose for the query and decided to use a combination of Apply and Pivot.

My new take on the script is simple, I will create a result set that will show a 1 for each server role for which a login is a member.  Each login will only have 1 row, opposed to the row per server role membership from the prior query.

The Script

[codesyntax lang=”tsql”]


In this example, the bulk of the heavy lifting is done through the pivot.  You will see that the Apply only seems to play a minor role in this script.  It is an important role nonetheless.  All logins (unless you have altered the public role) are also a member of the public role.  That membership is not displayed through the views I have used in this query.  Due to that handling of the public role, and to ensure that the membership in that role is not forgotten, it must be accounted for in the query.

This query will add a new column for the public role to those columns already presented via the pivot.  In this column we will show that every login returned via the query is a member of the public role.  But how is that done?  Using the apply, in really basic terms, acts like a join to this subquery that I use for the public column.  I am taking the value of 1 for column public, and applying that value to all of the results from the Pivot portion of the query.  I am using the Cross version and there is one more version – called Outer.  In my testing, both the Outer and the Cross Apply will return the same results for this particular query.

Some common uses for Apply are in conjunction with TVFs.  Should you use the Apply with a function where the “Join” criteria would be more tightly defined, then the use of Cross V. Outer will produce different results.  Just as with an Outer Join, the Outer would return results for all records in the “outer” or “left” part of the join whether there was a matching record in the TVF or not.  In the same sort of setup, the Cross Apply would only return records that had a match in the TVF.


Again, this is an oversimplification of the Apply statement.  My main goal was to present a use for Apply.  Apply can be a useful tool in the hands of the database administrator.  Examine it and learn what other potential it may have for some of your administration needs.

This is just one piece of the puzzle when performing a security audit.  Getting this information quickly when requested for an audit can be very helpful.  The output format is quite simple and very conducive for many auditors and management.  A simple output report for the Server Roles in your environment is merely one example of the usefulness of Apply.

SSIS Job Ownership

Comments: 2 Comments
Published on: April 11, 2011

I was strolling along one day when I saw somebody asking how to find out who owns a maintenance plan.  That evolved into finding out who owns the the job associated with the maintenance plan.  All of this in SQL 2005 and SQL 2008.

Well, we were stumped for a bit trying to figure the link between the job tables in the msdb database and the ssis table in the same database.  Linking the two together is not very obvious and we struggled with it for a bit.  After some research and trying this that and the other, I was able to come up with the below script.

[codesyntax lang=”tsql”]


This script is set to work out of the gate with SQL 2008.  Should you want it to work with SQL 2005 the change is simple.  Change the sysssispackages table to sysdtspackages90.  As you can see, the query joins the SSIS table to the jobsteps table with a pretty nasty string extraction.  There are other ways of extracting this information (I’m sure of it).  This works quite well for what it is intended.

Using this script, you can find out the jobowner, the packageowner, and the packagetype.  This is pretty good information to have on hand if you have several ssis packages that are stored in msdb and are run from a job.  One thing this script does not yet handle is if the SSIS file is stored on the file system.  Note that I only coded it so far to work with files stored in SQL.  When looking in the jobsteps table, you can tell the difference quickly by seeing that those stored in msdb have a /SQL at the beginning of the command string.  Those in the filesystem have a /FILE en lieu of that /SQL.

In a future revision I will work on parsing the package name out of that string that represents those stored in the file system.  And despite that nasty join, this runs quickly on my systems.  I am open to suggestions or other solutions that can provide this kind of insight.

SQL Server Role Membership

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: April 5, 2011

How well do you know the security in your SQL instances?  Do you know who has sysadmin level permissions?  SQL Server provides a few methods for you to find out who is a member of which roles at the server level.

For those that like to point and click, you can always navigate through the GUI (SSMS) to determine which users or groups have been granted access to the sysadmin fixed server role.  For those that want something a bit faster, you can use a script to return this information for you.  Just as with most things TSQL, there are numerous different ways of writing this script.  Here are some of those methods.

Verifying Server Role membership

[codesyntax lang=”tsql”]


With this script, I am querying the sys.server_role_members and sys.server_principals views.  For simplicity sake, I am also using the SUSER_NAME() function to derive the role name.  Note that I threw in a union all to get back the ‘Public’ group membership.  The public group is a special group that does not appear when querying the sys.server_role_members view – but everybody is a member.

An Alternative

[codesyntax lang=”tsql”]


This one is quite simple as well.  Note that I am not employing the use of the SUSER_NAME function but have used another join in its place.  I am also only interested in adding the public role at this time to SQL Users, Windows Users and groups that are not disabled.  That information in the where clause is optional and is present to demonstrate the ability to quickly pare down the results.

Another Option

This is really the easiest of the three queries.

[codesyntax lang=”tsql”]


I am still employing the union statement to populate the public role.  Notice the difference in the first half of the query though.  I am simply using the SUSER_NAME function for both principal_ids being retrieved from the server_role_members view.  This is a little easier to follow and write.  Performance considerations put this last query as the most efficient on my systems with the first query shared being a close second.

All of these will return your group memberships quickly and in a manner that is quickly understandable (names instead of numbers).  The use of a query such as these would be a stepping stone into auditing the permissions that are in place on your server.  It is also great to quickly validate who has sysadmin access and to use that to confirm that the account should have sysadmin access.

«page 2 of 3»

March 2018
« Jan    

Welcome , today is Wednesday, March 21, 2018