SQL

Who Owns Your Databases And Jobs?

Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI.

Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone.

You could attempt joining against sys.server_principals, however if there’s a user that doesn’t exist or is a part of a domain group they wouldn’t show up and you would just get a NULL returned. Try the following example:

SELECT  d.NAME ,

        sp.NAME

FROM    sys.databases d

        LEFT JOIN sys.server_principals sp ON d.owner_sid = sp.sid;

        

SELECT  s.NAME ,

        sp.NAME

FROM    msdb.dbo.sysjobs s

        LEFT OUTER JOIN sys.server_principals sp ON s.owner_sid = sp.sid ;

 

Now what, how can you quickly and easily get the owner? A little known system function will come to the rescue.

SUSER_SNAME (server_user_sid) will return the login name associated with a SID. Using this you can very quickly grab the information about who owns what without having to play with joins or get incomplete results:

SELECT  name ,

        SUSER_SNAME(owner_sid)

FROM    sys.databases;

 

SELECT  name ,

        SUSER_SNAME(owner_sid)

FROM    msdb.dbo.sysjobs;

 

There’s some great little hidden gems lying around in SQL Server like this. You just need to find them.

Rant

The Importance Of Good Documentation

Believe it or not I’m not actually talking about server documentation here (for an excellent post on that go read Colleen Morrow’s The Importance of a SQL Server Inventory).

I have spent the last 12 days dealing with a single production release. It is being considered a significant release, but to be honest it really isn’t. The biggest challenge has been to do with the way that the release documentation has been provided and the fashion in which the scripts have been built.

 

What I got

Here’s a brief example of a change request I’ve seen:

  • Change Request:
    • Update database – products (this links to a Sharepoint page)
    • Use code from this location (links to a file share)
  • Sharepoint page
    • Go to this location (but replace the middle part of the link with the link from the change request page)
    • Copy this subfolder to your machine
    • Follow the process on Sharepoint page 2 to deploy the code
    • Once Sharepoint page 2 is complete run script X
  • Sharepoint page 2
    • run script 1
    • run script 2
    • run script 3

 

Pretty painful right? Now multiply that by 8 for each of the database code deployments that needed to be completed. No fun, no fun at all.

 

What do I want?

It’s going to be a work in progress but we’ll be working with this particular dev team to put together a unified document to simplify the release structure.

Here’s what I want to see:

  • Change Request:
    • Update database – products – deployment instructions attached
  • Attachment
    • Deploy script 1 (link to script)
    • Deploy script 2 (link to script)
    • Deploy script 3 (link to script)
    • Deploy script X (link to script)
    • Rollback script (link to script)

 

The difference?

Instead of having to reference several different Sharepoint locations in addition to a change control document I now have a single document, attached to the change, which clearly defines the process for the release, the order for scripts to be executed, a link to each of those scripts and the relevant rollback information.

It’s not something that I think is too out of line to provide, but I’ve found the folks who have been providing releases in this method are extremely resistant to change. I can understand that, but to be fair, they aren’t the ones under the gun trying to put something in to a production environment in a consistent and stable manner.

I’ve lots of fun meetings coming up to talk about this.

 

What about you?

How do you get your change control documentation? Is it something plainly written and easy to follow? Or do you have to have a degree in cryptography to get code in to production?

SSRS

SSRS–Email Subscriptions Not Being Delivered

If you work with replication much I’m sure you’ve come across the situation whereby somebody created a publication and at some point in the future left the company. Once their AD account had been disabled or removed then the replication jobs, for some reason, would stop functioning and you would have to spend your time trying to figure out why. Eventually you would realize that the jobs had been mysteriously created under the context of that user account and so now longer work. A quick change so that jobs run under SA and everything is all happy happy again.

Well I had something similar happen this week with Reporting Services where all of a sudden email subscriptions were failing and critical reports were not being sent out. This is not the kind of call that you want to get during the night.

 

Identifying the failure was pretty easy, that was just a case of opening up the SSRS log file (by default found at C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesLogFiles) and looking through for the error:

 

emailextension!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation. —> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘<domain><user>’ are insufficient for performing this operation.
notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.Mail will not be resent.

emailextension!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation. —> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.Mail will not be resent.

 

 

Error in hand I went looking at the report in question. It had been created by the user who had left the company and so had two subscriptions. The first subscription was file based. That was having no trouble executing, which was interesting. The other, an email subscription was the one that was failiing. 

My belief at this juncture was that the file subscription, which has a user context for execution, was working ok because of that user context and the other was failing because the AD account of the old user was no longer around.

Going back on old knowledge I figured that I could just change the owner of the subscription and everything would be happy again and the reports would flow. I spent a good 30 minutes trying to figure out a way to do that. There’s actually no option, you have to drop and recreate the subscription. For me this would require changes to a lot of subscriptions, with several of them being data driven. Doing this would have taken an age.

Time to see if a meta-data update would help fix the issue.

 

I started digging through the ReportServer database and was able to come up with a list of subscriptions that were owned by the old user:

SELECT  u.UserName ,

        s.SubscriptionID ,

        Report_OID ,

        Description ,

        LastStatus ,

        EventType ,

        DeliveryExtension

FROM    ReportServer.dbo.Subscriptions s

        INNER JOIN ReportServer.dbo.Users u ON s.OwnerID = u.UserID

        WHERE u.UserName = '<old user>'

 

With this information in hand I went out and create a new AD account specifically for owning reports that would send emails. After making sure that the account had permissions to access the reports that it would subscribe to I went and updated the subscriptions so that they would run under this new security context:

BEGIN TRAN

BEGIN TRY

    DECLARE @newid UNIQUEIDENTIFIER 

    SELECT  @newid = userid

    FROM    ReportServer.dbo.Users

    WHERE   UserName = '<new user>'

    UPDATE  s

    SET     OwnerId = @newid

    FROM    ReportServer.dbo.Subscriptions s

            INNER JOIN dbo.Users u ON s.OwnerID = u.UserID

    WHERE   u.UserName = '<old user>'

END TRY

BEGIN CATCH    

    SELECT  ERROR_MESSAGE()

    IF @@TRANCOUNT > 0 

        ROLLBACK TRAN;

END CATCH

 

IF @@TRANCOUNT > 0 

    COMMIT TRAN;

 

This updated 73 reports for me and only took a couple of seconds. Much better than the couple of days it would have taken to try and change every subscription.

 

Knowing this I’ll be keeping a much close eye on subscription ownership in SSRS going forward.

Presenting

Speaking at SQLRally

I was fortunate enough to have one of my favorite sessions chosen for PASS SQLRally 2012 in Dallas.

I’ll be there presenting “PowerShell: Are you checking out my profile?”

I’ve given this presenation a couple of times in the past and it’s always been a lot of fun as it generally creates a lot of interaction with the audience. Hopefull you can join the audience this time and join me at the SQLRally on May 10th and 11th in Dallas (and don’t forget there are several great pre-conference all day sessions for you to attend).

I’m scheduled to be in the final slot of the day on Thursday 10th, so come see me before going to get your Gelato.

MCM

Achievement Unlocked–MCM: SQL Server 2008

If you had told me a year ago that I would attain the MCM certification for SQL Server 2008 I would have called you crazy and yet here I stand, a newly minted Microsoft Certified Master.

I first learned about the MCM back in 2010 when Brent Ozar (blog|twitter) wrote about his journey to becoming an MCM. At that point it was still a three week $18.5k brutal experience.

At the PASS Summit in 2010  Joe Sack (blog|twitter) gave a chalk talk about how he, as a program manager, was bringing big changes to the MCM certification and making it more accessible. This created a huge amount of buzz around the program. Something that had really seemed unattainable for a great many folks outside of Microsoft (you try getting your company to give you three weeks away for training let along having them pay for any of it) was suddenly achievable. When Robert Davis (blog|twitter) took over the program from Joe he continued as the public face of the certification and has worked very hard to keep it front and center with people.

The MCM: SQL Server 2008 now consists of a written knowledge and lab exam and costs just a fraction of what it did before.

AchUnl

If you have taken any of the other Microsoft certification exams you are kind of used to the exam format where they seem to be testing your ability to remember certain facts that you would have had to read in a book somewhere. There’s little in the way of independent thought processes that come along with gaining your SQL Server MCTS or MCITP certifications. Paul Randal had a pretty good rant on these lower level certifications.

The MCM exams are very different from this. The knowledge exam follows the same format as the lower levels with multiple choice questions however the questions are a lot more challenging. Rather than seeing if you can remember the syntax of a particular query you will get asked a question that has a broad scope and you have to take all of the information and provide the best answer (and you may have more than one correct answer, you just have to be sure that it fits all the criteria in the question).

The lab exam is a whole other beast. While this exam is very difficult it is, by far and away, my favorite exam to have taken. It’s real world SQL Server. You aren’t getting asked off the wall questions and you aren’t expected to recall that obscure syntax as Books Online can be used as a reference tool. I can’t go into any of the scenarios for obvious reasons, but be aware, if you’ve been working with SQL Server for a long time then you’ve probably run into a lot of the things you’ll be tested on. The questions will challenge you, you will need to carefully read the details of what’s required, but you won’t find any trick questions. I wish all exams could be this way (although I know with all the work that it takes to set them up that it’s just not possible).

 

On a more personal note this is a big deal for me. When you’re out in the world and job hunting and don’t have a degree to your name you tend to get stuck at the back of the queue. Now I might still be near the back of the queue but at least I can have confidence in my knowledge and ability to work with SQL Server.

A huge thanks go out to my good lady Cami (twitter) who not only had the belief in me that I could do this but also gave me the serious kick in the pants that I needed to actually get off my read and get it done.

SQL, SQL 2012

SQL2012 It’s The Small Things Pt4–User Defined Server Roles

Server roles have been around since before I started working with SQL Server however these roles have always been strictly defined and there has been no ability to either alter them or create new ones. SQL Server 2012 finally changes that and provides you with the opportunity to create server level roles that will let you provide greater permissions to logins without having to constantly deal with the minutiae of managing them on a login by login basis.

Let’s say for example you wanted to grant several logins the ability the view any definition. Normally this would require you to perform those grants on a very granular level. Now you can just create a server role and drop logins into that role:

USE [master]

GO

 

CREATE SERVER ROLE [SrvViewDefs]

GO

 

use [master]

GO

 

GRANT VIEW ANY DEFINITION TO [SrvViewDefs]

GO

 

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsqlJoe];

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsqlBiggles];

 

That’s some quick and easy management.

Here’s some examples of other permissions that can be quickly and easily provisioned to multiple users via user defined server roles:

  • Alter any linked server
  • Alter any login
  • Alter any server audit
  • Alter any server role
  • Alter resources
  • Alter server state
  • Alter settings
  • View any database
  • Shutdown SQL Server

It goes beyond basic SQL Server permissions, you can also allow server roles to impersonate logins or manage Availability Groups

USE [master]

GO

 

CREATE SERVER ROLE [SrvImpersonate]

GO

 

use [master]

GO

 

GRANT IMPERSONATE ON LOGIN::[SIRSQLnic] TO [SrvImpersonate]

GO

 

ALTER SERVER ROLE [SrvImpersonate] ADD MEMBER [sirsqlBiggles];

 

Anything that allows me to manage SQL Server more efficiently makes me happy. User defined server roles certainly make me happy.

SQL, SQL 2012

SQL2012 It’s The Small Things Pt3–End Of The Month Function

If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.

SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.

It’s very simple to use:

SELECT EOMONTH('2012/03/16')

image

 

Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:

SELECT EOMONTH('2012/03/16', -1)

image

 

It even handled the leap year correctly.

Simplicity itself. A great addition.

SQL, SQL 2012

SQL2012 It’s The Small Things Pt2–Unified Security Experience

Back when SQL Server 2005 came out there was a big change to the way that logins were handled. No more stored procedures to create a SQL login or to allow a Windows user or group to connect to SQL. It also brought the advent of allowing SQL logins to have the same kind of policy restrictions as you had in Active Directory.

Despite these huge changes for some reason Microsoft did not implement the same kind of changes when it came to roles. You still had to use stored procedures to grant role membership, this lead to an experience where you had to manage the syntax differently:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

CREATE ROLE RoleTest;

exec sp_addrolemember 'RoleTest', 'sirsqlDBAs';

exec sp_droprolemember 'RoleTest', 'sirsqlDBAs';

 

With SQL 2012 this old stored procedure way of managing things has finally gone by the wayside and we now manage roles in a similar fashion to logins:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

CREATE ROLE RoleTest;

ALTER ROLE RoleTest ADD MEMBER [sirsqlDBAs];

ALTER ROLE RoleTest DROP MEMBER [sirsqlDBAs];

 

This doesn’t just work with database roles. Also gone is the sp_addsrvrolemember syntax (which for some reason had you specify the login first then the role, opposite how sp_addrolemember did).

ALTER SERVER ROLE DiskAdmin ADD MEMBER [sirsqlDBAs];

ALTER SERVER ROLE DiskAdmin DROP MEMBER [sirsqlDBAs];

 

Managing roles this way makes a lot more sense than trying to do things via stored procedures. In an effort to ensure that old code still works Microsoft have left the old stored procedures in, but deprecated them, so don’t expect them to work in the next release. Anyway, wouldn’t you rather work to retool your code so that your security experience is more unified?

SQL, SQL 2012

SQL2012 It’s The Small Things–Default Schemas For Groups

If you’re reading this then I’m sure you know all about SQL 2012 which just went RTM and will be available for general release on April 1st. I’m sure you’ve also heard all about some of the big new changes such as AlwaysOn with readable secondaries, ColumnStore indexes, and FileTables. Missed in all the big announcements are some of the smaller things that make a huge difference in the day to day running of SQL Server. I’m going to put together a few posts outlining some of the smaller, but to me equally significant, changes that are in the new version.

 

Back in the day there was no facility to provide a default schema to a Windows Group, you would actually get an error were you to try:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

ALTER USER [sirsqlDBAs] with DEFAULT_SCHEMA = dbo;

 

image

 

This script now works in SQL 2012 and defines the default schema

image

 

If we check out the properties of the user we can see that the default schema has been set to dbo:

image

 

 

This is a very welcome addition. Prior to SQL 2012 you would have to create a separate login and user for each user in a windows group and then set the default schema for each, that’s a lot of administrative hassle eliminated.

SQL

The Modulo Operator Is Your Friend

Here’s another one of those sneaky little tricks that can be used to make your life easier.

I found myself needing to perform an insert of 240 million records from one table to another in the same database today. Rather than try to do this all in one transaction I wanted to keep the transaction log nice and small and so decided to do this in small batches (the database is in simple recovery).

Each batch would insert 50,000 records, with it only taking a few seconds to do each batch. Rather than sit there and have to execute this constantly I decided to throw the insert into a while loop with a begin/commit transaction and a checkpoint. I also wanted to know how many loops had been processed but didn’t want to overwhelm myself with data by printing every loop that was processed.

In the end I decided that knowing every 10 loops was more than enough information and so I put the modulo operator to good use.

 

In case you aren’t familiar with modulo (%) it simply returns the remainder when one number is divided by another. For example

SELECT 25 % 3

returns 1

 

In this case as I wanted to find out when the loop was divisible by 10 I just checked to see when the loop counter divided by 10 equaled zero (as there would be no remainder).

I threw that into my code and was able to quickly see how far along my processing was.

Here’s a quick example that you can just run to see what I mean.

DECLARE @msg NVARCHAR(255) ,

    @loopno INT = 0

 

WHILE @loopno < 1000 

    BEGIN

        IF ( @loopno % 10 ) = 0 

            BEGIN

                SELECT  @msg = 'Processing loop number '

                        + CONVERT(NVARCHAR, @loopno)

                RAISERROR (@msg, 0, 1) WITH NOWAIT

            END

        SET @loopno += 1

    END

 

As a DBA I really don’t think of using modulo often but it’s actually very useful.