MS12-027 and SQL Server
This morning Microsoft released MS12-027 as a 'Critical' update. After being scared by the advanced notification that a 'Critical' patch affecting SQL Server was coming out this week I was relieved to read it in its entirety.
The patch fixes an exploit in an office control that unfortunately is installed by default by all versions of SQL Server. They announced that there are reports of the issue being exploited in targeted attacks but I can't see how they affect the average dedicated SQL Server in the enterprise.
The attacks require you to visit a webpage or open an office document, so if no one logs on to your SQL Server hosts it would seem, with the information available right now, that you are all ok.
Still it's going to add to the workload this month, especially if you are running non-dedicated SQL Servers and the applications they share with use the windows components. You can read about some problems with 3rd party applications in KB2664258.
To top it off the patch is for all supported versions of SQL Server, from 2000 through to 2008R2.
SQL 2012 Certification
I've been studying for the SQL 2012 Certification and sitting the beta exams. I've done the first three DBA exams now with one to go later this week. 70-461, 70-462 and 70-464 all done, with 70-465 as the one I'm yet to do.
Anyone can sit the beta exams but they finish up this week, you can see details on them on the born to learn blog. While I've sat the 'administration' streams for SQL 2000, 2005 and 2008 I'd never done a beta exam. You don't get your results at the end of the exam session, they send them out sometime before the exam goes live
So far I've found the developer strain to be a bit more challenging and the administration exams to be pretty straight forward. Guess that shows where I sit skill-wise, I certainly do a lot more DBA work than development. The whole thing has been thoroughly enjoyable though.
My first real exposure to SQL 2012 was attending Greg Lowe's What's New in SQL Server 2012 course in February. Since then I have been pretty busy reading, testing and trying it out. If it turns out I pass all the beta exams, then I'll look to do the BI ones too, though that will be a real stretch since it looks very much focused on BI Developers rather than the infrastructure side.
SQL Agent not emailing
SQL Agent was not sending emails to the operator. The jobs were set, as part of testing, to 'E-Mail' the particular Opeartor 'When the job completes'. No email was being received. The jobs history, in Message, included "... NOTE: Failed to notify '{operator name}' via email."
The SQL Agent logilfe (SQLAgent.Out) had entries around each job completion time:
! [264] An attempt was made to send an email when no email session has been established
ReportServerTempDB growing, not clearing out
Recently I experienced a case where SSRS's ReportServerTempDB database which had been relatively stagnant, suddenly appeared to be growing at a fast rate. This was after a long period of the 'data size in use' fluctuating between reasonably small sizes
Date SpaceUsed(MB) SpaceAllocated(MB)
02/08/2010 3 200
02/09/2010 3 200
01/10/2010 6 300
01/11/2010 19 300
01/12/2010 445 500
02/01/2011 28 500
01/02/2011 605 700
01/03/2011 1624 1700
This was noticed, the DB was now 1.6 GB for data (and 2.6 gb for TLog). The database is in simple recovery mode, so the TLog on its own was worth investigating.
Identifying user owned database roles
In many databases I support the objects are all owned by dbo. If there is only one user schema (dbo) and there are objects that are owned by a user other than dbo then it's a mistake and can be corrected without ramification.
This is not necessarily the case with your environment, but if you want to quickly identify which roles aren't owned by dbo, or you've hit the following error when trying to drop a user, then the statements below should help:
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
This select statement lists out the roles that don't have dbo as their owner
select sp1.name, sp2.type_desc, sp2.name
from sys.database_principals sp1
inner join sys.database_principals sp2
on sp1.owning_principal_id = sp2.principal_id
where isnull(sp1.owning_principal_id,1) <> 1
order by 1
This next select generates the ALTER AUTORIZATION statements for all roles that don't have dbo as their owner to make them owned by dbo.
select 'ALTER AUTHORIZATION ON ROLE::' + quotename(sp1.name) + ' TO [dbo]; -- previously owner was: ' + QUOTENAME(sp2.name)
from sys.database_principals sp1
inner join sys.database_principals sp2
on sp1.owning_principal_id = sp2.principal_id
where isnull(sp1.owning_principal_id,1) <> 1
order by 1
If your problem was that a user couldn't be dropped because it owned a role, then after running the above script AND running its output against the same database, you should be right to re-attempt your drop user.
NOTE: There may be very good reasons why that particular user owned the role, just because you are seeing the above error doesn't mean that dbo should be the owner of that role. These statements fixed my problem in my environment and are not necessarily relevant to you.