SQLServerCentral.com | 2/8/2010 9:16:00 PM
I am excited to say I have been published on Simple-Talk. This was my first foray into being professionally edited. I will say the experience was not what I imagined. I guess everyone has different ideas of what it is like to have an editors review your work. I will say that my writing follows the 'train-of-thought' method (I think it's just how I'm wired), so my grammar did not go unnoticed. One of the most interesting things I learned out of this process is to take into consideration foreign readers. Many of the grammar comments explained that to a reader where English is a second language the wording might be confusing. The technical review also shed some light on what I wrote vs. what I was trying to accomplish. That second set of eyes helped me realize I took quite of bit of knowledge for granted, so I had to revise many of my descriptions and explanations. Would I write again? Most definitely! The hard part for me is finding the idea for the article. After that I just let the idea pour onto the paper and start the editorial process all over again.
SQLServerCentral.com | 2/8/2010 9:13:21 PM
Ok Guys and Gals. I know I have been a slow blogger thus far in 2010. To try and make up for it I have been speaking like crazy. I most recently spoke at the West Palm Beach SQL Server Users Group on Building Analysis Services Cubes in SSAS 2008. It was a great turnout and the crowd was good. Special Thanks to Scott Klein and the team from the group for puttin9g on a great night.
SQLblog.com - The SQL Server blog spot on the web | 2/8/2010 9:01:00 PM
In my last post in this series , I talked about the common habit of creating an IDENTITY column on every single table. Today I want to talk about a more broad concept: relying on undocumented (and therefore probably undefined, and certainly far from guaranteed) behavior and objects. ORDER BY in a view This is probably the most infamous of all SQL Server undocumented behaviors. In SQL Server 2000, users learned to create views with a built-in ordering, such as follows: CREATE VIEW dbo.MyView AS SELECT...(read more)
Dan's Blog | 2/8/2010 7:44:56 PM
Are you a web developer? Regardless if you use the MS stack, an open source stack, or a combination please take a few minutes to fill out this survey on web-based database management tools. It’s especially important if you use MySQL and PHPMyAdmin in a hosted environment. Seriously the survey is short. Thanks!
Journey to SQL Authority with Pinal Dave | 2/8/2010 7:30:15 PM
StreamInsight is a new event processing platform introduced in upcoming version SQL Server 2008 R2. Similar to other components such as SSIS, SSAS or Service Broker, it also needs to be installed along with the SQL Server. Up to SQL Server 2005, Microsoft’s main focus on SQL Server was to build a platform to efficiently store, [...]
SQLServerPedia | 2/8/2010 6:05:28 PM
For almost a year now, I’ve exchanged emails with the leaders of the Memphis SQL Server User Group, trying to find a time when our schedules would align themselves and I’d be able to join them for a meeting. After many attempts and several near misses, we’ve finally set a date! This Thursday, February 11, 2010, [...]
SQL Server Community Blogs | 2/8/2010 4:42:00 PM
Hi,Just a short post to mention the Microsoft Architect Insight Conference at Microsoft's London office (Cardinal Place) at the end of March.This is the fifth annual Architect's conference and is packed with presentations from Microsoft Program Managers, Architects and MVPs.I'm presenting a session on the first day on SQL Server Consolidation. If this is something that interests you - drop me an email or sign-up for the conference.Regards, Justin Langford - Coeo LtdSQL Server Consultants | SQL Server Remote DBA
Hi,
Just a short post to mention the Microsoft Architect Insight Conference at Microsoft's London office (Cardinal Place) at the end of March.
This is the fifth annual Architect's conference and is packed with presentations from Microsoft Program Managers, Architects and MVPs.
I'm presenting a session on the first day on SQL Server Consolidation. If this is something that interests you - drop me an email or sign-up for the conference.
Regards,
Justin Langford - Coeo Ltd
SQL Server Consultants | SQL Server Remote DBA
SQL Server Community Blogs | 2/8/2010 4:14:21 PM
Orcsweb are kindly upgrading the hosting account that we are using for SQLBits. This is so that we can host all the great videos we have of SQLBits, and hopefully do more. If you are considering getting hosting then Orcsweb are very good and there support is fantastic. A big thank you to Orcsweb for providing us this service
Orcsweb are kindly upgrading the hosting account that we are using for SQLBits. This is so that we can host all the great videos we have of SQLBits, and hopefully do more.
If you are considering getting hosting then Orcsweb are very good and there support is fantastic.
A big thank you to Orcsweb for providing us this service
SQLblog.com - The SQL Server blog spot on the web | 2/8/2010 1:14:00 PM
According to T-SQL Tuesday rules as ratified by me in the first and second T-SQL Tuesday posts, the T-SQL Tuesday host is supposed to post a roundup within two days of the end of the event. So a reasonable person should expect a roundup to be posted by the second Thursday of the month. It gives me no pleasure to admit that I've been completely unreasonable and have totally dropped the ball. I'm twothree four weeks late. (I actually started the post two weeks ago. That's bad. And now I'm forced to...(read more)
SQL Server Community Blogs | 2/8/2010 11:28:00 AM
Download (FREE!) Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 3 for developers and database administrators from http://webcasts.sqlworkshops.com. Preview, full webcast will be available for download coming Wednesday around noon PST. Please forward the link to your friends and local user community.Webcast 3: Recommends not using stored procedure or other plan caching mechanism like using sp_executesql and Prepared Statement using ADO.NET or OLEDB based executions for memory allocating queries. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union.With examples provides ways to identify queries performing Hash match operations that spill to tempdb. Using SQL Profiler: Hash Warnings (Hash Recursion and Hash Bailout). Using sys.dm_exec_query_memory_grants: Granted Memory, Used Memory and Maximum Used memory. Explains how SLEEP_TASK wait type is associated with Sort Warnings.Explains with examples how sp_recompile can block and bring an application to a standstill and recommends using DBCC FREEPROCCACHE (plan_handle) instead.Explains with examples how stored procedure or other plan caching mechanism affects queries that sort (perform order by). Explains how IO_COMPLETION wait type is associated with Sort Warnings.Demonstrates why rollbacks and database restore could wait on IO_COMPLETION wait type and if needed how this wait can be reduced.Explains how parallel query performance is significantly affected by a CPU intensive query executing on one of the CPU cores. Explains the reason for the performance issue and how to identify the issue. With example explains the reason for the observed query execution time when the child thread executes on the CPU core where the CPU intensive query executes and also when the coordinator executes on the CPU core where the CPU intensive query executes. Recommends ways to avoid this parallel query performance issue and also demonstrates that only certain queries will be affected by this and not all parallel queries.Explains sys.dm_os_waits_stats.signal_wait_time_ms does not indicate system wide CPU pressure, just at a CPU core level.Explains the prefetch mechanism and how it can affect query performance. Provides ways to force prefecth. Demonstrates cases where Avg. Disk Sec / read, the disk latency, PAGEIOLATCH_SH, Avg. disk queue length is very high and the query executes fast.Explains why Plan Guides (Plan Freezing!) cannot help with prefetch or in few other cases.Webcast 2: Explains high CXPACKET waits are NOT a direct result of delays associated with inefficiencies of parallel processing. Provides example to scale queries over many CPU cores without reducing MAXDOP settings.Webcast 1: Explains Memory allocation issues with sort. Demonstrates ways to identify sort spills to tempdb. Provides query rewrite procedure to avoid sort spills to tempdb. Demonstrates cases where 1 tempdb date file per core might not be ideal for all implementations.RegardsR Meyyappanhttp://www.sqlworkshops.com
SQLServerCentral.com | 2/8/2010 10:33:00 AM
If you haven't seen the Simple-Talk newsletter (it's free, so sign up!) you may not have seen my article on their site. http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/ Writing this article has been probably the most fun I have had writing. Basically, I had to take the winning solution to a T-SQL programming challenge and try to figure it out and explain how it works. I also compared the winning solution to three other solutions that are easier to write and understand. If you are having a hard time convincing yourself that row-by-row processing isn't that bad, this article should convince you otherwise. In my job, I inherited several stored procedures that run once per night that would take 10 minutes or more to run. Besides the time they took to run, they could also leave some of our applications in non-working order if the job happened to fail. For example, the proc to add new users to our document management system (DMS) first would disable all users. Then set up a cursor that updated each user with current information and re-enabled the active employees. If anything went wrong in the job, for example an unexpected NULL value in the data from our HR system, the entire office could be locked out of the DMS. The the proc could never be run during business hours and there was lots of hard-coding that made the proc difficult to maintain. So, within a few months I rewrote these problematic procs using set-based code instead of cursors. I also corrected the faulty logic so the the procs could run anytime if we needed them to and set up a table to hold the data that used to be hard-coded. A stored procedure that used to take 10 minutes, now runs in about 10 seconds. I didn't use any of the phreaky techniques that the winner of the contest used, but just eliminating the row-by-row processing made my process very efficient!
If you haven't seen the Simple-Talk newsletter (it's free, so sign up!) you may not have seen my article on their site. http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/ Writing this article has been probably the most fun I have had writing. Basically, I had to take the winning solution to a T-SQL programming challenge and try to figure it out and explain how it works. I also compared the winning solution to three other solutions that are easier to write and understand. If you are having a hard time convincing yourself that row-by-row processing isn't that bad, this article should convince you otherwise.
In my job, I inherited several stored procedures that run once per night that would take 10 minutes or more to run. Besides the time they took to run, they could also leave some of our applications in non-working order if the job happened to fail. For example, the proc to add new users to our document management system (DMS) first would disable all users. Then set up a cursor that updated each user with current information and re-enabled the active employees. If anything went wrong in the job, for example an unexpected NULL value in the data from our HR system, the entire office could be locked out of the DMS. The the proc could never be run during business hours and there was lots of hard-coding that made the proc difficult to maintain.
So, within a few months I rewrote these problematic procs using set-based code instead of cursors. I also corrected the faulty logic so the the procs could run anytime if we needed them to and set up a table to hold the data that used to be hard-coded. A stored procedure that used to take 10 minutes, now runs in about 10 seconds. I didn't use any of the phreaky techniques that the winner of the contest used, but just eliminating the row-by-row processing made my process very efficient!
SQL Server Community Blogs | 2/8/2010 10:27:00 AM
When working in an environment that utilises a proxy server you may see the following error when trying to connect to a Report Server using Reporting Services Scripter"The request failed with HTTP Status 407. Proxy authentication required."In order to fix this you need to create a configuration file for RSScripter.exeCreate a file called RSScripter.exe.config in the same folder as RSScripter.exeCopy the following into the file and replace http://yourproxy with your proxy details<configuration> <system.net> <defaultProxy enabled="true" useDefaultCredentials="true"> <proxy bypassonlocal="True" proxyaddress="http://yourproxy"/> </defaultProxy> </system.net></configuration>You can read more details about this error and how to correct it for Visual Studio here http://support.microsoft.com/kb/910804
When working in an environment that utilises a proxy server you may see the following error when trying to connect to a Report Server using Reporting Services Scripter
"The request failed with HTTP Status 407. Proxy authentication required."
In order to fix this you need to create a configuration file for RSScripter.exe
<configuration> <system.net> <defaultProxy enabled="true" useDefaultCredentials="true"> <proxy bypassonlocal="True" proxyaddress="http://yourproxy"/> </defaultProxy> </system.net></configuration>
You can read more details about this error and how to correct it for Visual Studio here http://support.microsoft.com/kb/910804
SQLServerPedia | 2/8/2010 9:30:07 AM
Recently I have been doing an audit on our enterprise SQL Servers including production and non-production systems and wanted to check the server level configurations. One of the audit items I specifically wanted to look at was the server level default connection options. Unless there is a strong reason to do so, In general it [...]
Carpe Datum | 2/8/2010 9:17:59 AM
OK, there are probably a dozen or more of these kinds of posts, but I’ll dive in anyway. From time to time, people send me e-mails or comments on this blog asking for help. Sometimes it’s on the topic at hand, and other times the topic just jogs their memory about something else. Often I’m happy to help. If I know the answer without doing any research (or even if I have to do a little) I’ll interrupt what I’m doing and dash out a note with the answer. But of course I have a job (three, to be exact) and so any time I help with a question I’m lengthening my day, spending less time with my family, and so on. If you think about it, everyone that you ask (and everyone I ask) is in the same boat – when someone helps me, I am taking their most valuable asset: their time. So I learned a very important lesson very early on: Make it easy for people to help you. So here’s the steps to follow to do that – it really isn’t that hard:Request, don’t demand. I got an e-mail yesterday (on Sunday) where someone found a blog entry about one topic, and basically said this about another topic: “Tell me where I can find ‘x’ so that I can alter it.” Guess what I did? That’s right, I hit the delete key. If you are asking a question from a professional, you need to understand that they normally get paid – very well, sometimes – for their time. Make sure your question is a question, not a demand. Be clear about the problem. Vague statements don’t help – and very few people have the time to dig the real question out of you. Be specific. Ask the single question you really need help with.Keep the problem limited. “Rewrite my code for me” isn’t going to happen. “help me with this line” might. “Where do I go to find out more about the SELECT statement” is even better. If your problem takes more than a few minutes for someone to answer, then you should probably get someone on-site to help you.Explain what you’ve already done. This, of course, means you’ve already actually done something. What have you looked up, what do you already understand, where have you looked, what have you tried?Be polite. Please and Thank You are magic words, whether you get the answer you were looking for or not. I’ll add one thing when you’re responding to a blog – not just mine, anyone’s. If you want to ask a question, ask it as a reply to a post, not an e-mail. The author wants to answer the question once, and it’s almost a guarantee that you’re not the only one with that question. Also, other readers might know the exact answer and help you even more. I know, you have to register, all that stuff. Just consider it the price of getting your answer.
OK, there are probably a dozen or more of these kinds of posts, but I’ll dive in anyway. From time to time, people send me e-mails or comments on this blog asking for help. Sometimes it’s on the topic at hand, and other times the topic just jogs their memory about something else.
Often I’m happy to help. If I know the answer without doing any research (or even if I have to do a little) I’ll interrupt what I’m doing and dash out a note with the answer. But of course I have a job (three, to be exact) and so any time I help with a question I’m lengthening my day, spending less time with my family, and so on. If you think about it, everyone that you ask (and everyone I ask) is in the same boat – when someone helps me, I am taking their most valuable asset: their time. So I learned a very important lesson very early on: Make it easy for people to help you.
So here’s the steps to follow to do that – it really isn’t that hard:
I’ll add one thing when you’re responding to a blog – not just mine, anyone’s. If you want to ask a question, ask it as a reply to a post, not an e-mail. The author wants to answer the question once, and it’s almost a guarantee that you’re not the only one with that question. Also, other readers might know the exact answer and help you even more. I know, you have to register, all that stuff. Just consider it the price of getting your answer.
SQLblog.com - The SQL Server blog spot on the web | 2/8/2010 8:17:59 AM
OK, there are probably a dozen or more of these kinds of posts, but I’ll dive in anyway. From time to time, people send me e-mails or comments on this blog asking for help. Sometimes it’s on the topic at hand, and other times the topic just jogs their memory about something else. Often I’m happy to help. If I know the answer without doing any research (or even if I have to do a little) I’ll interrupt what I’m doing and dash out a note with the answer. But of course I have a job (three, to be exact) and so any time I help with a question I’m lengthening my day, spending less time with my family, and so on. If you think about it, everyone that you ask (and everyone I ask) is in the same boat – when someone helps me, I am taking their most valuable asset: their time. So I learned a very important lesson very early on: Make it easy for people to help you. So here’s the steps to follow to do that – it really isn’t that hard:Request, don’t demand. I got an e-mail yesterday (on Sunday) where someone found a blog entry about one topic, and basically said this about another topic: “Tell me where I can find ‘x’ so that I can alter it.” Guess what I did? That’s right, I hit the delete key. If you are asking a question from a professional, you need to understand that they normally get paid – very well, sometimes – for their time. Make sure your question is a question, not a demand. Be clear about the problem. Vague statements don’t help – and very few people have the time to dig the real question out of you. Be specific. Ask the single question you really need help with.Keep the problem limited. “Rewrite my code for me” isn’t going to happen. “help me with this line” might. “Where do I go to find out more about the SELECT statement” is even better. If your problem takes more than a few minutes for someone to answer, then you should probably get someone on-site to help you.Explain what you’ve already done. This, of course, means you’ve already actually done something. What have you looked up, what do you already understand, where have you looked, what have you tried?Be polite. Please and Thank You are magic words, whether you get the answer you were looking for or not. I’ll add one thing when you’re responding to a blog – not just mine, anyone’s. If you want to ask a question, ask it as a reply to a post, not an e-mail. The author wants to answer the question once, and it’s almost a guarantee that you’re not the only one with that question. Also, other readers might know the exact answer and help you even more. I know, you have to register, all that stuff. Just consider it the price of getting your answer. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
SQLblog.com - The SQL Server blog spot on the web | 2/8/2010 8:05:00 AM
I'll be presenting two sessions this week (February 9 and 11, 2010), both on SQL Server Indexing. The first is for the PASS AppDev Virtual Chapter, and will take place on Tuesday at noon EST. You can attend the meeting via this link: https://www323.livemeeting.com/cc/usergroups/join?id=8PQHN2&role=attend The second will be the same presentation at the Northeast Ohio SQL Server Users Group at the Microsoft office in Independence Ohio. You can reserve a seat here: http://www.bennettadelson.com/seat.aspx?sig=sql&ID=210...(read more)
SQLblog.com - The SQL Server blog spot on the web | 2/8/2010 6:00:00 AM
Introduction This post is the ninth part of a ramble-rant about the software business. The current posts in this series are: Goodwill, Negative and Positive Visions, Quests, Missions Right, Wrong, and Style Follow Me Balance, Part 1 Balance, Part 2 Definition of a Great Team The 15-Minute Meeting This post is an introduction to metaproblems and discusses a common metaproblem: drama. Problems I define a problem as an unsolved issue. It could be minor. Or it could threaten your project, llivelihood,...(read more)
SQLServerPedia | 2/8/2010 5:42:00 AM
As some of you may have heard, I'm starting a new job this month, which means leaving my current job. It occurred to us that this is a topic rarely (if ever) really covered, so we made a video about leaving jobs, and assorted other topics. Today we just have an overview of the new DBAs @ Midnight video, "Breaking up with your Boss" (in 4 parts). But tomorrow we'll have a blog on companies and relationships, in good time to participate in Adam Machanic's T-SQL Tuesday! In all fairness, we don't get to the actual topic until 5 minutes intp Part 2, but we have some jolly good fun in all. Your overview: Breaking up with your Boss, Part 1: Worst secret handshake EVAR.When Good IPs go Bad: Sean's IP spoiled, and we rant about Time Warner Cable. Surprise ending!Next rant: Having to give your info to clerks over and over again. Breaking up with your Boss, Part 2: We envision a brave new world of not filling out forms 3x each.We make Star Trek Next Generation Data/SQL puns...Around about 4:15 I talk about my new, upcoming job5:30 We get on How To Break Up With Your Boss (6:15 Sean spills tea) Feeling bad for your boss: Bad time to leave? It's never a good time to can you, either.Feeling bad for the company: You are barcode DBA, and an expensive one, at that.Feeling bad for the project: Feelings fade quickly!13:20 So how do you break up, then? [Office Space reference here.] For starters, separate yourself! Breaking up with your Boss, Part 3: Why I got nervous the day I quit.I mention Mladen Prajdic, a Twitter friend who amused me (and mangle his name, badly) I mention The Gift of Fear by Gavin DeBecker, and how quitting is like breaking up, is like safety rules...take away footholds.Sean tells a big secret about his past, and we all learn a life lesson.We learn THE wording to use when leaving a job.We discuss various responses to "Why are you leaving?"Sean's Blue Cross story - 3 months, and never logged into a single database. Jen rants. Sean finishes his story...almost. Breaking up with your Boss, Part 4: Conclusion of Blue Cross story.Exit interviews.Celebrity status, and entering the SQLPass starfield with a guide. "JOE FREAKING CELKO, who is one of the coolest dudes on the planet...".Different ways of leaving a job - the nice breakup, and THE BAD ONE (Jen's story).Things you can do to mitigate a bad breakup - this is THE #1 MOST IMPORTANT TIP when leaving a job. And a couple of other good tips too.We reiterate: Sometimes the answer is no. Sean's story of "fixing" Arcserve backups. "Oobi...backup expert!"We finally, finally trail off and say goodnight. Didn't see that coming, did you? Happy days... -Jen McCown http://www.MidnightDBA.com
As some of you may have heard, I'm starting a new job this month, which means leaving my current job. It occurred to us that this is a topic rarely (if ever) really covered, so we made a video about leaving jobs, and assorted other topics. Today we just have an overview of the new DBAs @ Midnight video, "Breaking up with your Boss" (in 4 parts). But tomorrow we'll have a blog on companies and relationships, in good time to participate in Adam Machanic's T-SQL Tuesday!
In all fairness, we don't get to the actual topic until 5 minutes intp Part 2, but we have some jolly good fun in all. Your overview:
Breaking up with your Boss, Part 1:
Breaking up with your Boss, Part 2:
Breaking up with your Boss, Part 3:
Breaking up with your Boss, Part 4:
Happy days...
-Jen McCown
http://www.MidnightDBA.com
SQL Server with Mr. Denny | 2/8/2010 5:00:20 AM
Recently one of my developers asked me to begin returning data from a function as XML instead of a comma separated list. My thoughts on this was awesome, another cursor getting removed from the system. I'll write a quick FOR XML AUTO, ROOT('root') at the bottom of the SELECT statement being used to generate the [...]
SQLServerPedia | 2/8/2010 5:00:20 AM
SQL Server Community Blogs | 2/8/2010 4:28:36 AM
I do get frustrated sometimes by the very poor user experience you get with management studio. My most recent annoyance comes when looking at job histories, if you want to look at the details of the step that failed you have to go and find the job, open it, find the job step and open it. If you've got loads of jobs this can take some time. Especially with the popup window behaviour of management studio. This is a connect item that would allow us to double click on an item in the job history and open the job properties directly. I reckon for a moderately busy DBA this would save days of effort per year. http://connect.microsoft.com/SQLServer/feedback/details/531889/allow-access-to-job-details-from-the-log-file-viewer-to-improve-manageability If you think this would be a good suggestion then please vote for it.
I do get frustrated sometimes by the very poor user experience you get with management studio.
My most recent annoyance comes when looking at job histories, if you want to look at the details of the step that failed you have to go and find the job, open it, find the job step and open it. If you've got loads of jobs this can take some time. Especially with the popup window behaviour of management studio.
This is a connect item that would allow us to double click on an item in the job history and open the job properties directly. I reckon for a moderately busy DBA this would save days of effort per year.
http://connect.microsoft.com/SQLServer/feedback/details/531889/allow-access-to-job-details-from-the-log-file-viewer-to-improve-manageability
If you think this would be a good suggestion then please vote for it.
SQLblog.com - The SQL Server blog spot on the web | 2/8/2010 1:58:00 AM
Hello every body. This is my first blog on that great site so I am really exciting.I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to another account. That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.The solution we found was to start SQL Server with single user mode. As Raul said that using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Hello every body. This is my first blog on that great site so I am really exciting.
I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to another account. That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.The solution we found was to start SQL Server with single user mode. As Raul said that using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
SQLblog.com - The SQL Server blog spot on the web | 2/7/2010 11:33:00 PM
Back in October, I started a series of blog posts called " Bad Habits to Kick ," and thought I would revive the theme. I've worked with developers that dabble in SQL, and they tend to have a few common traits. I'm not sure where they come from, but one that I find rather distracting is the tendency to place an IDENTITY column on every single table. Usually this is done because this is an "easy" way to add a column to the table that allows you to identify a single row. Now don't get me wrong, I am...(read more)
SQLServerPedia | 2/7/2010 11:00:50 PM
I am excited to say I have been published on Simple-talk. This was my first foray into being professionally edited. I will say the experience was not what I imagined. I guess everyone has different ideas of what it is like to have an editors review your work. I will say that my writing follows the 'train-of-thought' method (I think it's just how I'm wired), so my grammar did not go unnoticed. One of the most interesting things I learned out of this process is to take into consideration foreign readers. Many of the grammar comments explained that to a reader where English is a second language the wording might be confusing. The technical review also shed some light on what I wrote vs. what I was trying to accomplish. That second set of eyes helped me realize I took quite of bit of knowledge for granted, so I had to revise many of my descriptions and explanations. Would I write again? Most definitely! The hard part for me is finding the idea for the article. After that I just let the idea pour onto the paper and start the editorial process all over again.