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.

Security Audit

Comments: 2 Comments
Published on: March 19, 2010

Of late I have seen a lot of questions on how to audit the logins and users on each SQL Server.  I had the same questions for myself when I went through the same exercise some time ago.  My first step was to peruse the internet and see what I could find to get me started.  I found that to be quite helpful.  I found a lot of different scripts that were beneficial.  I, like most, did find one though that I preferred above the rest.  That script can be found here.

Why do I like this script?  I like the format.  It also generates a nice output that can be passed along to auditors.  The output is saved into an html format and seems more presentable to me.  Besides those facets, it meets the base requirements – I can find what roles and users have what permissions in each database on a SQL Server Instance.

The script didn’t quite suit all of my needs.  I think that is frequently the case.  The trick is being able to take the script and make necessary adjustments to suit whatever needs you may encounter.  The changes that I made to this script were in favor of progressing toward an automated inventory solution that I could run from a central location.  The script as it stood required manual intervention.  Granted, I have not yet completed my inventory solution, I have modified the script to work well with 2000 and 2005 and output the results to a properly consumable html file.  Since 2000 and 2005 behave differently in certain regards, I had to add some logic for the script to also behave differently if depending on the version of SQL Server it was run against.  This was necessary since I have SQL 2000 – SQl 2008 in my environment.

Scripts of Change

So, starting from the top.  I decided to use several more variables and create a bunch of temp tables.  The variables will help in the decision making, and the temp tables will help in Data storage for processing as the script runs.  Thus we have this block of code at the top in place of the old Variable block from the original script.

[codesyntax lang="tsql"]


That is the prep setup so we can now begin the true work of the script.  As, I said there was some decision logic added to the script.  I needed to find a way to determine SQL Server version and based on version execute a different script.  And now we have the decision block.

[codesyntax lang="tsql"]


Basically, I am checking the version and determining if I should use the SQL 2000 objects or if I can use the SQL 2005 objects since the 2000 objects are scheduled for deprecation.  Also, since xp_cmdshell is disabled by default in SQL 2005, I am prepping to enable that just for the final piece of this script.  Due to the nature of xp_cmdshell, it is advisable that you understand the security risk involved and revert it back to disabled – if you enabled it to run this script.  There are other methods for doing this, I am sure, but I chose this since I got consistent results and have not had time to revisit it.

After that decision tree, I have changed the main body of the script to also use a decision tree in building the dynamic sql.  That tree is built like the following snippet.

[codesyntax lang="tsql"]


I think you can see at this point some of the differences and why I chose to do it this way.  The final section of code change comes at the end of the script.  This is where the html file is finally built, and then saved out to the file-system.

[codesyntax lang="tsql"]


In this section, I am enabling xp_cmdshell if necessary.  I am also performing one more necessary trick.  I am using xp_cmdshell to flush bad dns records and ping a remote host.  I will be saving the file off to a central repository and found some bad dns records on my servers while doing this process.  By adding this step, I saved myself quite a bit of frustration in the long-haul.  After that, I use xp_cmdshell to bcp the results out to file.

[codesyntax lang="tsql"]


This took some work to get the ” ‘ ” all lined up correctly and working properly with BCP.  It was somewhat satisfying when it finally came together.

Now, remember I said you should reset xp_cmdshell back to disabled once completed?  Well, I built that into the script as a part of the cleanup.  I perform this action right before dropping all of those tables that I created.

[codesyntax lang="tsql"]



I effectively took a well working script and made it suit my needs / wants just a little better.  The initial code was just over 300 lines and I nearly doubled that with this script.  Is it worth the extra effort?  Yes!  Though it took some time and effort to make these modifications, I was able to finish auditing the servers well ahead of pace of doing it by hand.  Furthermore, I can still use this script and continue to reap the benefits of having taken the time to modify it.  Can the script be improved?  Sure it can.  I have a few things in line for it currently.  The biggest piece of it will be modifying it to be run from the inventory package I am still trying to finish in my spare time.

You can download the script in its entirety here.

Edit: Fixed some WP formatting issues.

page 1 of 1

October 2014
« Sep    


  • @awanderingmind: Anyone have an idea why I'm not seeing any LoginTimers records in sys.dm_os_ring_buffers? Only Error and ConnectionClosed. #sqlhelp
  • @pshore73: #SQLHelp In @olahallengren Index script, can I use a query to pass results to the database parameter? Need to skip a DB for Index Maint.
  • @DoktorKermit: @sqlstudent144 I think there's a few legacy systems out there, so I would do it, but not i great details #sqlhelp
  • @SQLTaiob: Could not cleanup worktable IAM chains to allow shrink or remove file operation. Checked tempdb usage and nothing longrunning. #sqlhelp
  • @sqlstudent144: Do enough people still use DTS that it's worth blogging on a DTS bug? #sqlhelp
  • @DoktorKermit: .@clausmunch Have you tried restarting, and if that it's not helping - try reinstalling #Trolling #IknowClaus #sqlhelp
  • @clausmunch: Anyone has some insights into worktables in Tempdb? It seems as we have an issue where they don't dissapear in TempDB #sqlhelp #sqlfamily
  • @mellie81: Indeed @sql_handle @SqlrUs no time, that's why I wondered. Maybe there's a big nogo on a detach?Just hope it's a timesaver for me. #sqlhelp
  • @AskSQL: Permissions on folder look same but somehow SQL cannot write to folder. Anyway to sort of resync permission on folder for SQL? #SQLHELP 2/2
  • @AskSQL: Data drive had issues so I stopped SQL & moved folder somewhere then new drive added (same drive letter). SQL is not starting #SQLHELP 1/2

Welcome , today is Thursday, October 2, 2014