Somedays it is easier to blame the users

I believe that part of a DBA's job is to educate their users on proper SQL Server practices.  Today, I had a situation occur where a developer rolled out a database that nearly crashed its SQL Server, because every query had a where clause that contained a wild card at the beginning of the search string. 


WHERE product like '%widget%'


This meant that every call that used this pattern had to perform a table scan and caused all sorts of locking and crappy performance.  I was not happy.  


I started to write a "You are an idiot" email to the developer screaming about how stupid they were and why did they not use a full-text index.  I started to look through my SQL Server training materials for the section where I covered full-text indexes and realized that I had never written about this.   Now, should an experienced developer know that using a wildcard at the beginning of a search string is a bad idea? I think so, but I also had not done my job fully and trained the developer.  This is what I believe is called a teachable moment.  


Understanding database performance is not easy or everyone's databases would be super fast.  Before blaming someone else make sure that you have communicated what you expect of them first.  Then the next time they do something stupid, you are justified in flying off the handle.

Upcoming Speaking Events

I have submitted sessions for the upcoming SQL Saturday events in Columbus, OH and Indianapolis. I am looking forward to attending both events and hopefully will have the opportunity get back into speaking about my favorite topic SQL Server again.

Public Not Granted Server Permission

I have been setting up policies on my 2008 servers. I thought a good place to start would be with the suggested policies Microsoft provides with their samples. You can download the sample databases from codeplex.

I can came across a sample policy named “Public Not Granted Server Permission”, now this sounds like something a person would want to implement. I imported the policy and ran it on my test system and discovered my system failed, but I was not really sure why. The policy explains that the server role public has been granted server level permissions. OK, but what does that mean? What server level permissions? This policy does not provide an option to force compliance, so the dba must correct the issue manually, but how and if you correct what permissions will be revoked?

I started by trying to investigate the @PublicServerRoleIsGrantedPermissions facet, but that provided no greater information. Then I found this great blog post by Randy Dyess who delves into the guts of the public role. He provides a script to identify the permissions that the public role has been granted. Using this script I determined that a fresh install of SQL Server by default grants the public role the following permissions:

Name	Permission	Class	Securable	State
public	CONNECT	ENDPOINT	NAMED_PIPES	GRANT
public	CONNECT	ENDPOINT	SHARED_MEMORY	GRANT
public	CONNECT	ENDPOINT	TCP	GRANT
public	CONNECT	ENDPOINT	VIA	GRANT
public	VIEW ANY DATABASE	SERVER	Server	GRANT

Of these permissions only VIEW ANY DATABASE is the only server lever permission. I am familiar with this permission. If you DENY VIEW ANY DATABASE to PUBLIC a user will only be able to see the master, msdb and databases in which they own. This would prevent a user from seeing the other databases on the server listed in management studio. The problem with this is that I don’t make any user dbo. If a developer wanted to connect via Studio Manager they would not see database which they have been granted permission within. They can still connect to the database, but can’t see it in Management Studio which kind of defeats the point.

For my testing I went ahead and issued the following:

DENY VIEW ANY DATABASE to PUBLIC.

I re-evaluated the policy and it still failed. So on the advice of Brian Kelley, I issued the denied all connect permissions to the Endpoints.

DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] to public

DENY CONNECT ON ENDPOINT::[TSQL Local Machine] to public

DENY CONNECT ON ENDPOINT::[TSQL Default TCP] to public

DENY CONNECT ON ENDPOINT::[TSQL Default VIA] to public

This worked and the policy passed. However, this is not a policy that I will be implementing.

Speaking at Louisville PASS Chapter

On Thursday June 11th from noon to 1pm I will be speaking at the Louisville PASS chapter meeting. The meeting is being held at Kindred Healthcare, 680 S. Fourth Street. The topic is "Defensive SQL Server Security." I believe that Idera will be providing Pizza.

Set Number of Error Logs via code

In my ongoing effort to automate everything. I wanted to have a script that would change the default number of error logs. After some looking around in help and on the internet, I realized I was going to have come up with a solution on my own. I was complaining to Ryan about having to do some real work, when he suggested that I just run a trace when I make the change via Management Studio.

Here is the result:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10

I am sure using this is not recommended, but then MS should have provided me a better method to accomplish this.

32-bit to 64-bit SQL 2008 Upgrades

I am not sure if it is a symptom of my advanced age, but I am forgetting more and more. I know that you can't do an inplace upgrade from a 32-bit version of SQL 2005 to a 64-bit version, but I did indeed forget. I am running into this issue more frequently on SQL 2008 upgrades than I did with previous version, becuase SQL 2008 offers Workgroup Edition 64-bit for the first time. Many of the customers running Workgroup Edition SQL 2005 in a WOW configuration are looking to upgrade.

The only workaround that I knowis to:

• Script out logins, jobs, and any objects that are stored in MSDB and Master
• Detach user databases
• Uninstall 32-bit version of SQL Server
• Reboot Server, just to be sure
• Install 64-bit version of SQL Server
• Create logins, jobs and other server wide objects
• Re-attach user databases

New MVP

Congratulations to my friend Chris Shaw who was finally award an MVP for SQL Server. Chris has been working for years supporting the SQL Server Community and I am very happy for him.

ps. Chris is a non-douchebag MVP