• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.
  • Advertisements

Database Security Roles in msdb

All databases have a standard set of “fixed” database security roles which have been available since SQL Server 2000 and hopefully, even if you are new to SQL Server, you are familiar with this set:

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

Back in the day, the msdb database was primarily used for managing backups, SQLAgent jobs, and DTS packages. And, other than the TargetServersRole role for multi-server job management via a master server and target servers, there were no other database roles defined for helping establish a separation of duties security model for tasks controlled via msdb

In SQL Server 2005 some long awaited new roles were added to msdb (http://msdn.microsoft.com/en-us/library/ms188283(SQL.90).aspx) which help with segregating permissions for managing SQLAgent Jobs, DTS\SSIS, Database Mirroring, and Database Mail (the replacement for SQL Mail):

  • DatabaseMailUserRole
  • db_dtsadmin – renamed in later versions as db_ssisadmin
  • db_dtsltduser -renamed in later versions as db_ssisltduser
  • db_dtsoperator – renamed in later versions as db_ssisoperator
  • dbm_monitor – does not appear until database mirroring is implemented
  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Then, in SQL Server 2008 even more roles appeared as msdb took on increased importance in the management of new features such as the CMS (Central Management Server), PBM (Policy Based Management), Data Collection, and MDW (Management Data Warehouse).

Thus, the following new roles may be found in SQL Server 2008+:

In SQL 2008 R2 one more set of roles was added for the Server Utility (UCP) feature:

  • UtilityCMRReader
  • UtilityIMRReader
  • UtilityIMRWriter

The above UCP roles are primarily assigned via the Server Utility configuration wizard and Utility Explorer Security tab. The “Utility Reader role” referenced in the Security tab is equivalent to the UtilityCMRReader role. As you may notice, certain service and administrator accounts are automatically in this role and cannot be changed (grayed out).

And, now in SQL Server 2012, believe it or not, there are no new roles in msdb!  So, if you haven’t kept up with all the changes in msdb in the past few versions, this is your chance to catch up and make sure that you are fully utilizing these features and roles as needed in your environment. For a recap of all the features which utilize msdb, you can also refer to an earlier post I wrote – “What’s in Your msdb?”.


Discontinued Features in SQL Server 2012 – Part 4

In the previous installment on this topic, I looked at discontinued features in SQL Server 2012 which may mostly impact your administration scripts.  Today, we are widening the net and looking at discontinued features which may impact either the application layer or administration programs and scripts. Most of these items have been deprecated as far back as SQL Server 2000, so it isn’t like they have gone away overnight. However, if you are still running on SQL Server 2000, then you really haven’t had a need to go replace them…until now.

SQL Mail is finally gone! It would have been totally fine with me if SQL Mail had been discontinued as soon as Database Mail was added in SQL Server 2005.  However, you do lose functionality (receive mail processing) with Database Mail, but the trade-off is much improved reliability, security, supportability, and scalability. For example, you no longer need an Extended MAPI client (e.g. Outlook) installed; you don’t run the risk of attacks via reading mail; and Database Mail is supported on clusters. So, if you are still using the read\process mail functionality of SQL Mail, you’ll need to find another method for your application to use before upgrading to SQL Server 2012.  If all you are doing are send mail tasks, including notifications from SQLAgent, then the switch to Database Mail is a no-brainer and you can do it now in SQL Server 2005/2008/2008R2. However, this is more than just a configuration change; it does require usage coding changes for example from xp_sendmail to sp_send_dbmail.  

Unless you’ve been stuck on SQL Server 2000, you have probably already started converting applications using SQL-DMO (Database Management Objects) to use SQL-SMO (SQL Server Management Objects) when you found you wanted to access newer features in SQL Server 2005+ that were not available via SQL-DMO.  If you want to use your SQL-DMO scripts against SQL Server 2012 – you must convert to SQL-SMO; SQL-DMO is discontinued.

If you have databases in compatibility mode 80, it might be because the code using them is still using the old non-ANSI outer join syntax “*=” or “=*”.  This syntax is not supported in later compatibility modes and because compatibility mode 80 is not supported in SQL Server 2012, this syntax is no longer recognized in SQL Server 2012 in any scenario.  You’ll need to get all of this code up-to-date with current ANSI join syntax used in the FROM clause.

Another T-SQL syntax which is disappearing is the COMPUTE / COMPUTE BY clause in SELECT statements; you will need to convert these statements to use the ROLLUP option of the GROUP BY clause.

If you tried to speed up the results returned to your client or force a particular query plan by using the FASTFIRSTROW query hint, you’ll need to switch to the OPTION (FAST n) syntax as FASTFIRSTROW is no longer available.  For more info on the overall impact of using this option, see this blog from the SQL Server Query Optimization team and this one from SQL MVP Grant Fritchey. As with all query hints, you should re-evaluate these periodically as they may no longer be as beneficial as when originally added.

In SQL Server 2012, you can no longer invoke an ad-hoc error message number using RAISERROR integer ‘message’ syntax as in: RAISERROR 50111 ‘Sample error message’.  You’ll need to convert to the RAISERROR() syntax:  RAISERROR (‘Sample error message’, 10, 1). But, if you want to really get your code current, convert to TRY…CATCH (available since SQL Server 2005) and use THROW (new in SQL Server 2012). There are some differences in behavior between RAISERROR and THROW and these are documented in BOL’s THROW entry.

I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here.

Happy upgrading!

SSRS Log Files Retention Bug

Last October (2010), I posted the following bug on Microsoft Connect (https://connect.microsoft.com/SQLServer/feedback/details/615123/ssrs-2008-r2-keepfilesfordays-config-file-item-does-not-appear-to-work).  Essentially, the SQL Server Report Server (SSRS) 2008 R2 KeepFilesForDays parameter in the ReportingServicesService.exe.config file isn’t working as advertised in BOL for deleting expired log files.  And, until today, when I went to manually delete them, I still had every log file since I installed in June 2010!

A few weeks ago, Microsoft officially closed my Connect item as “Fixed”. But, no word on when/where it is fixed.  I still see the problem in SQL 2008 R2 SP1 and I don’t see a mention of a fix for this in any of the three (3) CU’s released for SP1 so far.  It also appears that this is likely a problem in SSRS 2008 and SSRS 2005 based on a handful of inquiries I spotted in various forums when researching.  Although in SSRS 2005 the parameter setting and log files are in different locations due to SSRS’s usage of IIS for that version.  I also checked my Denali CTP3 installation of SSRS and sure enough – every log file since I installed (way, more than the default 14 days) still exists.

So, just a word of warning, if you assume that this parameter is working for you – it probably isn’t and you’ll need to find another way to delete your old log files for SSRS. If you have plenty of space on the drive where these are stored, then it isn’t an issue – yet. But, beware!

The Case of the Odd CPU

I love mysteries – always have.  I think I had read every volume of Alfred Hitchcock and The Three Investigators and of course Nancy Drew by the time I was finished with fourth grade. I loved trying to figure out the answer to the mystery before it was revealed. Of course, the authors of these mysteries (and even the ones I read today) always held back some key detail that they didn’t reveal until the end that helped to put all the pieces together and solved the puzzle.

A while back, my team was presented with a “mystery”.  Our rock solid automated installation process for SQL Server 2005 was suddenly failing intermittently and we had not made any changes to our installation scripts. But, something had obviously changed in the environment that was causing the failures.

After troubleshooting the SQL Server installation logs for error messages, we found we had encountered a known problem which thankfully already had a documented workaround (http://support.microsoft.com/kb/954835).  The change causing the problem was that we were now getting virtual servers delivered with an odd number of CPUs as seen by the OS and SQL Server. For the additional CPU\core scenarios which can cause an installation failure of SQL Server 2005, please reference the KB article. This issue has actually been documented several years, so it isn’t a new thing, but it was a new condition to us in our environment. If you still find the occasional need to install SQL Server 2005, then you may have a good chance of encountering this condition if you haven’t already.

However, just performing the workaround wasn’t the end of the story for us.  Like any good mystery, there are often “loose ends” to tie up once the main mystery is solved. In our case, we have a post-install configuration script to allocate multiple tempdb files based on a ratio to the number of processors detected. But, our script, which was also applicable to our SQL Server 2008 environment, expected the number of processors to always be a power of 2! As we learned the hard way – that may not always be the case now.

Lessons learned\confirmed:

  • If a process breaks which worked previously, something changed!
  • The impact of the “change” may reach beyond whatever drew it to your attention initially.
  • Expect change and when possible plan for it.

SQL Server Deprecated Features – Part 3

In this third installment of my mini-series on SQL Server deprecated features (see part 1 and part 2), I will look further at tools to help detect the usage of deprecated features.

SQL Server 2000

For SQL Server 2000 instances, you are pretty much stuck with using the Upgrade Advisor tool for the version of SQL Server to which you are upgrading. In addition, you need to read BOL Backwards Compatibility (links available in part 1) as a reference and manually search your code and database configurations.

SQL Server 2005

Starting with SQL Server 2005, Microsoft has added some detection capabilities into the SQL Server Profiler tool.  Two new event classes were added to report “Deprecation Announcement” feature usage and “Deprecation Final Support” feature usage.

Per the text inside Profiler when “Deprecation Announcement” is selected: Occurs when you use a feature that will be removed from future version of SQL Server, but will not be removed from the next major release of SQL Server…

Per the text inside Profiler when “Deprecation Final Support” is selected: Occurs when you use a feature that will be removed from the next major release of SQL Server…

If you setup your profiler trace to record to a table (I named mine Deprecated_Features_Trace_tb), then you can query it as follows:

SELECT * FROM [dbo].[Deprecated_Features_Trace_tb]  
WHERE EventClass IN (125,126)

EventClass 125 corresponds to “Deprecation Announcement” and EventClass 126 corresponds to “Deprecation Final Support”.

Ironically, you may find that you want to use a Column Filter on ApplicationName to exclude the Profiler tool and SQL Agent (and maybe other internal Microsoft features) which are currently using deprecated features themselves.  For example, when recording the profiler results to a table, the table is created using ntext and image datatypes which have been deprecated and are therefore reported events. Alternatively you could filter on DatabaseName and exclude the system databases.  However, you need to be absolutely sure that no one has created non-Microsoft supplied objects in those databases if you chose to filter on database name.

SQL Server 2008

In SQL Server 2008, an explicit performance counter object has been added for deprecated features with specific “instances” of deprecated features. This enables you to use the DMV sys.dm_os_performance_counters to query if the usage of any of these features has occurred within your SQL Server 2008 instance.

SELECT * FROM sys.dm_os_performance_counters 
WHERE [object_name] LIKE '%:Deprecated%' AND cntr_value > 0

Of course, this won’t tell you where the deprecated features are used, but at least you will know whether you need to go hunting in-depth.

Another new feature in SQL Server 2008 which can help with this task is Extended Events. You can setup an Event Session to capture both “deprecation_announcement” events and “deprecation_final_support” events.  If you are new to Extended Events, then you must read Jonathan Kehayias’ old blog and/or new blog – these links point specifically to all of his Extended Event tagged entries.  Jonathan has written a “XEvent a Day” series and much more. If you want to learn about Extended Events – he’s the man!

Final Thought for Detecting Deprecated Features

For all versions, however, no matter what tools you’ve initially used, always, always, always run the Upgrade Advisor.

SQL Server Deprecated Features – Part 2

In part 1 of this mini-series on deprecated features, I explained what that meant and where to find more information. This entry will focus on issues I’ve personally encountered in this area which require close attention not just to deprecation, but actual discontinuation.

While rewriting all of our “data collection” scripts in PowerShell and SQL-SMO (because SQL-DMO was discontinued in SQL 2008 R2), I discovered an unexpected “backwards compatibility” issue.  When I ran the database collection script against certain SQL 2000 servers, the script “failed” against some databases. My first suspicion was that the databases were “offline” and I needed to add a check for that condition.  However, I added that check and still got failures. When I explicitly connected to the instances with the problem databases using SQL Server Management Studio (SSMS), I got a semi-surprise – they all had databases in 65 compatibility mode… and those were the databases which caused my script to fail.   So, instead of pulling the database compatibility setting via SQL-SMO, I now have to select it using the Invoke-Sqlcmd SQLPS cmdlet using a T-SQL statement and verify that it is greater than 65 before continuing with the collection of the database’s metadata.

What surprised me was that this happened on a SQL 2000 instance.  I knew that SQL-SMO was not supported on SQL 6.5 instances, but it did not occur to me that it would also be invalid for a database in 65 compatibility mode on an instance whose versioning supported SQL-SMO.

At that time, I decided against collecting further metadata from 65 compatibility mode databases.  There are too few to justify the additional scripting required.  The main thing is that we will be able to report how many 65 databases are still around.

Consequently, the next issue I realized was that the SSMS tool cannot even manage a database in 65 compatibility mode as SSMS uses SQL-SMO almost exclusively behind the scenes. Remember, I’m not in a day-to-day production DBA role to have encountered this scenario.

Then, I found another “backwards compatibility” issue when using the SSMS 2008 R2 to script a drop/create of a table from a database on a SQL 2000 instance.  The database containing the table was in 80 compatibility mode (so not quite the same issue as previously discussed).  What I noticed in the auto-generated T-SQL statement was that the “exist” check for the table prior to the drop, did not select from the SQL 2000 system tables, but from the newer system views in SQL 2005 and up.  Consequently, if I had tried to execute the generated script, it would have failed since sys.objects does not exist in SQL 2000.  I have verified that this has actually been the case since SSMS 2005, so hopefully everyone besides me was already aware of this gotcha.

Bottom line: be very aware of the version of the tools you are using to access down-level instances and down-level databases.  In the words of Ronald Reagan, “Trust, but verify”.

SQL Server Deprecated Features – Part 1


Okay – there is still officially a couple of weeks to go, but here in Southeast Texas, the redbuds have bloomed and azaleas are next!  As we move from winter to spring – many people (in the Northern Hemisphere, anyway) think of “spring cleaning” and time to clean out the garage and clean out their wardrobe – you know – throw away all the stuff you’ve kept for years thinking you’ll use it or fit back into it. But, of course, expiration dates have come and gone, and even if you could fit into that outfit from 10 years ago – you’d get some strange looks on the street.

It is the same with our databases – every now and then we have to check the expiration dates.  With SQL Server “Denali” on the horizon, I thought that a mention of old features of SQL Server which are not / will not be in newer versions was an appropriate topic to tackle.

This concept is referred to as “deprecation”.  According to The New Oxford American Dictionary, the verb “deprecate” means to “express disapproval of”.  Thus, when Microsoft lists a feature as “deprecated” they are expressing disapproval in the continued usage of that feature with the intent to remove the feature entirely in a future release.

With each release of a new version of SQL Server, Microsoft documents “Backwards Compatibility” with prior versions. This documentation is typically divided into four components – deprecated features, discontinued features, breaking changes, and behavior changes.  All four areas are important to review when moving to a new version of SQL Server. However, it isn’t like Microsoft pulls the plug without warning.  Discontinued features were previously deprecated, so you usually have at least one release (i.e. several years!) to address these known issues.

It is especially important to note whether deprecated features are disappearing entirely or if you just need to start using a different syntax.  For instance, in SQL Server 2005 many of the object management system stored procedures (e.g. sp_addlogin) were deprecated and replaced by equivalent CREATE <object> and ALTER <object> T-SQL commands.  You can still use the old feature for another version (or sometimes two), but it will not be enhanced if a new parameter or option is added in a new release.

The Upgrade Advisor for each version will attempt to scan your database instance for code or settings which will be impacted by a new release.  However, that obviously means that code has to reside in the database in unencrypted stored procedures. So, yes, usually you have to find your own way to scan all of your scripts, programs, etc. for the obsolete commands.

If you focus on replacing\discontinuing the deprecated features over the course of each release, you will be ahead of the game when those features are actually discontinued in a future release.

To review the Backwards Compatibility issues documented for each SQL Server version, check the MSDN online books shown here:

One of the biggest issues to be aware of when planning to upgrade\migrate from SQL Server 2000 to SQL Server 2008 is that SQL Server 2008 will not support databases whose compatibility mode is not at least 80. In my experience, there are still a lot of databases sitting on SQL Server 2000 instances running in 60, 65, and 70 compatibility modes!  Come on people – you’ve had 10 years to get those updated to 80 compatibility mode!  Of course, you probably thought those systems would be retired by now, didn’t you? Or you thought that you’d be retired or in another job and it would be someone else’s problem….or maybe you just had the bad luck of inheriting someone else’s obsolete system.  In any case, the first order of business before going any further is to figure out how to get those databases running successfully in compatibility mode 80.  Good luck!

And, don’t forget once you get those 80 databases migrated to SQL Server 2008 in compatibility mode 80, you’d better work on getting them fully into 100 mode!  Microsoft has stated that each version will only support two levels down for an upgrade. Thus, when Microsoft SQL Server “Denali” rolls out, it will only support 90 and 100 database compatibility modes for upgrades.