File System Rights Needed to Attach a Downgrade DB

This falls into the category of things I probably should have already known, but for some number of reasons (not all my fault!), I didn’t.

Last Thursday night was an honorary Friday night at The Hideaway, since we took last Friday off (I got screwed at work on a day of for New Year’s this year), so I was trying to catch up on some blog posts in my reader. One of them included some code I wanted to run, but I found out that I didn’t have AdventureWorks attached to SQL after the last rebuild of the main server at home. The first step was going to be fixing that, so I moved the files from their old location to my new standard folder and set off to Attach…

Before this story gets too long…

The DB wouldn’t attach to the SQL 2008 instance. I was using my old SQL 2005 version of AdventureWorks, because it was already on the machine and at least for now, I want to keep it around. It was complaining about the files or the DB being set to Read-Only, and it can’t do an upgrade on a Read-Only DB. I needed to fix the DB or the file system permissions.

The DB wasn’t read-only when I killed the server before the rebuild, so that wasn’t it. Had to be NTFS permissions.

Checked those and the group that the SQL Service Account had Modify permissions on the folder where the files were. Checked this a few times to make sure I wasn’t crazy. I tried to search out some help on this, but I was having a hard time getting the search terms right to find anything useful. I finally wound up breaking out ProcessMonitor to help figure out what was going on.

Turns out, SQL Server was using (impersonating?) the Windows account I had connected to the instance with when it was reading the files. That account doesn’t have write access to the folder(s) in question, so any writes it was trying to do were failing. That’ll do it.

I don’t understand why it does this. Nothing is mentioned in BOL about this, and some real quick Googling didn’t bring up anything, either. Since I pretty much work only with SQL 2000 and SQL auth all day right now, I don’t know if this is new in 2008. At my last job where I had 2005, whenever I would do an operation like this, my Windows account would have been Local Admin on the server, so I wouldn’t have run into it.

I did some quick testing and although it of course still impersonates the Windows Auth user when you attach a DB that doesn’t need upgrading, as long as the user you’re logged on with has read rights, you’ll still be in business.


Obviously giving write rights to the Windows account you’re logging on to SQL with will fix this, but that doesn’t strike me as a good idea if you don’t have those rights in the first place. I mean, you don’t have those rights for a reason, right (principle of least privilege, etc)? But, if it’s a Windows Auth-only situation, that’s the only way to do it with the account in question.

Another way around it, is to use SQL Auth. This is what I wound up doing, mainly because I wanted to test to see if that works. As mentioned, if the Instance isn’t in Mixed Mode Auth, this isn’t an option. Also if, for whatever reason, creating a new account for this purpose isn’t allowed, then this option also doesn’t help you.

I’m sure this is old news to most everyone else, but it caught me by surprise. Lesson learned!

“Well I made a mistake today”

Getting mails from a Developer that start like this almost always leads to awesome. This turned out to be one of the times when it wasn’t as awesome as it could have been, but did give me the opportunity to spread some knowledge (which I don’t get to do very often, because, well, I’m not that smart).

This situation was the old, “oops, WHERE clauses are a good idea with DELETE statements.” The good news is that this was in Development, so it wasn’t a giant fire. Although I didn’t see the message right when it came in, I did see it in time to get to it before that night’s backup ran (we just keep one backup file in Dev and overwrite it every evening). I probably could have pulled from Production or Test instead of restoring a 140+ gig DB for a 299 row table, but we’ve got the space, more IO than God, and it was a Friday night where nothing else was going on out of the ordinary. Table restored, life goes on.

Actually, there were a couple points that I was able to make with this situation.

First: Tell your DBA when things go bad!

In our situation, with the backup file getting overwritten every night, if a Developer makes a mistake like this, they have to let us know before 8:00 the day of in order for us to be able ion do anything about it. The guys/gals have to first realize something bad happened, and then get to us right then in order to recover. If they sit on it until the next day, it is too late.

Second: BEGIN TRAN is your best friend.

When running DML, manually start and end transactions. Sure, SQL Server has the nice, easy implicit transactions that you don’t have to worry about, but those can become your worst enemy very easily. All it takes is either missed highlighting before mashing F5 or an unfortunately-placed closing paren.

BEGIN TRAN? (skip this paragraph if you already know) By default, SSMS uses implicit transactions. This means that even though you don’t type it out, when you run statements, SSMS begins a transaction, runs your stuff, and then commits it. By manually starting a transaction with BEGIN TRAN in front of your UPDATE, DELTE, or whatever, you retain control of this instead of letting the UI do it for you. This means you can run your statement(s), check the results, and then COMMIT or ROLLBACK yourself. In short, this is manual transaction control.

This one takes some diligence, because it’s easy to be complacent. I’m doing a simple little UPDATE statement, I didn’t make any mistakes, everything will be fine. Of course you think that—you wouldn’t run any statements that you didn’t think were right, would you? This is why you have to tell yourself to type BEGIN TRAN every time. It only takes once to really ruin your day.

OK, Third: COMMIT TRAN until it throws an error

This is another tip that I learned from our senior DBA on probably my first or second day on the job. Basically, when you commit your user transaction, keep trying to commit it until SSMS reports an error (trying to commit a transaction when there isn’t one open). Why? Glad you asked!

Create a table & put a couple rows of data in it:
ID      INT     IDENTITY(1,1),
Name    VARCHAR(20)     NOT NULL

SELECT 'Smythee'


Next, say you want to delete Bob from the table. Bob was never any fun anyway, was he? Because you’re heeding the above advice, you are going to wrap this simple one-row delete in a Transaction. You run the following:


   FROM TranTest
WHERE Namee = 'Bob'

Whoops, you fat-fingered the column name and didn’t notice until you ran it, and it threw an error.

Fix it and run it again:


   FROM TranTest
WHERE Name = 'Bob'

This runs OK, you double-check the contents of the table, everything looks fine.

Next step is to run COMMIT TRAN. That runs without error, and you go on your merry way.

But, there’s a problem: Select @@trancount and see what you get. You should see one transaction still open. Why is that?

When the first statement was run, a transaction was opened. Even though the statement itself bombed because of the bogus column name, that transaction is still there. When you fix it, if you run BEGIN TRAN again, you will now have a nested, second transaction. Running a single COMMIT will commit your changes, yes, but it still leaves one transaction open. Because that transaction still has locks, it will block other statements looking to operate in the TranTest table.

Moral of the story? Mash F5 on COMMIT TRAN until SSMS throws an error.

What was I talking about again?

Oh right, our poor developer.

In the mail I sent back to him, I commended him for being smart about letting us know right away when a mistake was made, as it allowed us to actually get the data back (or mostly so). I also recommended manual transactions, because they can save your tail.

I don’t know if he’ll take the advice to heart, but he at least has the tools available to him now if he wants to use them.