Note: some images appear cut off in the basic blog view. To see the full-width images, please use the link to the printer-friendly version at the end of the post. Apologies for the inconvenience.

File under: letting myself serve as an example for others.

I recently built a test box with an instance of SQL Server 2008 Developer Edition. I've done this so many times that I apparently got sloppy and stopped paying attention during setup. This time it caught up with me. On first launching Management Studio to do some post-install configuration, I realized that I'd "locked myself out" - I skipped the step to add my account to the sysadmin role on one of the instances. Setup requires that some account be added as sysadmin, but I didn't remember what I'd chosen, and there were no other accounts on the box that stood out as likely candidates.

I'd also opted to stick with the default Windows authentication mode, meaning the built-in sa account was not available.

For those who haven't made the move to SQL 2008, you'll find the following step in the Setup program. Unlike in earlier versions, local Windows admins aren't automatically granted access to SQL Server 2008. You'll want/need to designate one or more account to be added to the sysadmin server role. Skipping that step somehow got me in this mess.

I really wanted to avoid the time it would take to uninstall and reinstall the instance. Happily, I remembered another way: as a member of the local Windows Administrators group, I could start the SQL instance from a command line with the -m parameter for single user mode, connect to the service, and let myself in the "back door". ("m" for single user? Sure, that makes perfect sense. I remember this as "mono" for one at a time.)

One of the side effects of starting in single user mode is that members of the local Windows Administrator group can connect to SQL Server with its one connection. (Whether this is a good idea from a security standpoint remains open for debate, but it sure saved my bacon this time.) This would give me the ability to log in, map my Windows account into a SQL Server login, and add my new login to the sysadmin role. Here are the steps I followed.

1. Using the SQL Configuration Manager, I stopped the instance I needed to work with.
   
   
   

2. I also stopped any other SQL-related services which might try to connect to the instance, using up my one connection. This included Agent, and might also include Analysis Services or Reporting Services, depending on your installed options.
   

3. From a command prompt, I navigated to the SQL Server folder where the program executable lived.

4. I started SQL Server's executable with the -m parameter and waited for it to complete startup and recovery of any databases. (Note that the error log information echoes to the command prompt window but will simply pause when it's done startup - leave this window open to keep SQL Server running in this single-user mode. You can confirm if you successfully launched in single-user mode by looking for a line in the output that reads "SQL Server started in single-user mode. This an informational message only."
   
   
   

5. From a query tool, I connected to SQL Server using my Windows account. While I could have used a new query window in SQL Server Management Studio, I didn't want to wait for it to load, so I used SQLCMD from another command prompt session. (You can't use the Object Explorer pane in SSMS to connect when in single-user mode: clickers beware! This is one of those times where knowing the code (or having a saved script) is essential) If you're unfamiliar with SQLCMD, the -S switch specifies the instance name, the -E specifies that you're using Windows Authentication. These switches are case-sensitive.
   

6. Once connected, I created a login for my Windows account:
   

7. Then I used a system stored procedure to add my account to the sysadmin fixed server role.
   

8. Now that I've let myself back in to SQL Server, I stopped the service with the SHUTDOWN command. I could have closed the other command prompt window as well.

9. Finally, I restarted the service. Since I already had a command prompt window open, I opted for Windows' NET START command. The SQL Configuration Manager, the Windows Services Control Panel, or the Registered Servers pane in SSMS would also have worked.
   

And here's my visual confirmation (I could have simply tried to reconnect using SQLCMD, but I think we're all tired of command prompt screenshots by this point). Note that I used the system function IS_SRVROLEMEMBER to check my status. Looking at the properties of my login, or of the sysadmin role in Object Explorer would have shown this as well.

I hope this helps someone else in the same predicament!

Posting this for my own reference:

SQL Server MVP Glenn Berry asked today on Twitter whether there was a command-line switch needed to enable upgrading from SQL Server 2008 Enterprise Evaluation Edition to a full Enterprise edition.

I replied (incorrectly, as it turns out) that it wasn't possible, as I remember reading in Books Online that the old /SKUUPGRADE parameter was no longer supported in SQL 2008.

But I do love Twitter for this kind of thing: Rambling DBA (and MVP) Jonathan Kehayias weighed in with a correction. It seems /SKUUPGRADE has morphed into /ACTION=editionupgrade, as Jonathan provided in the following example:

Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=MSSQLSERVER /PID=

I'm filing that one away for later, and I'm much obliged to Glenn for posting the question that exposed a hole in my knowledge, and to Jonathan for filling in the gap!

I'd like to announce the details of this fall's certification study group for the Denver SQL User Group. For the fall session, we're going to focus on the DBA exam(s):

Exam 70-432: Microsoft® SQL Server® 2008 Implementation and Maintenance or Exam 70-431: Microsoft SQL Server 2005 Implementation and Maintenance

The book we'll be using is:

Microsoft Press: MCTS Self-Paced Training Kit (Exam 70-431) (http://tinyurl.com/lv8fy3)

or

Microsoft Press: MCTS Self-Paced Training Kit (Exam 70-432) (http://tinyurl.com/nx52mr)

We'll meet over Live Meeting, with only one in-person date on the schedule, the day of the tests.

• Exam Review Live Meeting #1: Saturday Oct 24, 9-11am

• Exam Review Live Meeting #2: Saturday Nov 7, 9-11am

• In-person exam cram and test-taking: Saturday Dec 5 (specific times to be announced, but exam cram will be in the morning, and exams offered in the afternoon)

This schedule is open enough that we can add another review meeting (via LiveMeeting) as needed.

Free Practice tests will be made available to active participants after the Live Meeting exam review sessions have completed and well before the exam date.

Participants will need to register themselves for the exam at Prometric but please do not do so yet, as we will be setting up a private exam room at Ameriteach on Dec 5, and you will need that location code to be able to register.

We will be setting up a web forum and document repository at the new denver.sqlpass.org website. Registration information will be sent to study group participants shortly.

I've been trying to set up a "kickoff" LiveMeeting to discuss this with anyone who has questions, but communications and scheduling snafus have prevented me from doing so until today. I'll host a LiveMeeting at lunchtime on Monday and Tuesday, Oct 12 and 13, for anyone who wants to go over the program and ask questions.

If you would like to participate in this study group, please send an email to the group with the subject "Certification Study Group" so I can be sure to catch the reply.

As a followup to a question in this week's 2790: Troubleshooting and Optimizing Database Servers using Microsoft SQL Server 2005 session, here are some additional resources on using SQL Server tools to monitor blocking:

• Knowledgebase article Q271509 How to monitor blocking in SQL Server 2005 and in SQL Server 2000
• Adapting SQLDiag to invoke sp_blocker_pss_80 (and some other interesting ideas)
• Top SQL Server 2005 Performance Issues for OLTP Applications SQLCAT - Top 10 Lists
• SQL Server Wait Types, Perfmon Counters, and Correlations

• Product team blog: http://blogs.msdn.com/sqlpbm/
• Lara Rubbelke's blog: http://sqlblog.com/blogs/lara_rubbelke/default.aspx
• SQL Server 2008 Compliance Whitepaper