SQL Server Administrator locked out of own database server

It embarrasses me to say that this happened again recently. We were building a new SQL Server 2005 server & locked it down before we had added our Administrator group & privileges to the server.

In the old days, I had assumed that I needed to reinstall SQL Server again from scratch … but I haved learned better since then! Since it has happened again, I decided it was worth blogging.

a) Start by granting a user account temporary local windows administrator privileges

b) Stop and restart the SQL Service, through the Services management console , using the -m (or -f) option. This places SQL in single user mode.

MSDN – http://msdn.microsoft.com/en-us/library/dd207004.aspx

c) Log into SQL from the command line (using the account in (a) above)

C:\>OSQL -Sservername\instance -E

d) Create a login for the Windows group

1>CREATE LOGIN [<domainName>\<win_group>] FROM WINDOWS;


(MSDN- http://msdn.microsoft.com/en-us/library/ms189751.aspx)

e) Give SysAdmin privilege

1>EXEC master..sp_addsrvrolemember @loginame = N’domainName\win_group’, @rolename = N’sysadmin’



and exit OSQL

f) SQL Server (& the Agent) can then be restarted without the -m to bring it back into regular service

g) TEST!!! … that the windows group can access SQL Server with system administrator privileges

h) Remove local windows privileges from the account used in (a)

In my last scenario, it was a cluster. So we had to offline the cluster SQL Service using the Cluster Admin tools. We made the -m change and started the service through the Services console. In (f) we restarted SQL using the Cluster Admin tools again.

Remember to restart any other services dependent on SQL as well.


CloudBusting -Dispelling the myth of the Cloud

Galway’s ITAG are organising a one day (& evening) event this September & all are welcome ….

The Cloud is impacting all aspects of our modern lives,whether you are in business, a consumer , or a developer.

So what do you need to know about the cloud?This day-long tech conference hosted on the GMIT campus (http://www.gmit.ie/ ) and supported by HP,aims to tackle that question examining the technologies and services offered in the cloud.

Cloud computing is listed as one of Gartner’s top 10 strategic technology trends to address.”Cloud computing is a major technology trend that has permeated the market over the last two years. It sets the stage for a new approach to IT that enables individuals and businesses to choose how they’ll acquire or deliver IT services, with reduced emphasis on the constraints of traditional software and hardware licensing models,” said David Cearley, vice president and Gartner Fellow. “Cloud computing has a significant potential impact on every aspect of IT and how users access applications, information and business services.”

Learn about the differences of IaaS, PaaS, SaaS and Private, hybrid, public cloud offerings and hear from your peers on their experiences when it comes to developing for cloud applications and their journeys in getting there.

This event is people in various roles of IT and business .It will be a “cross-over “ day will attempt to cover to the whole picture with tracks on startups, web, devops, social media, music.

The intended audience is people who want to learn more about the cloud from developers,managers, architects, web designers,entrepreneurs, sysadmins,devops, IT professionals and students.

Admittance to this event is free but registration is required at http://cloudbusting.eventbrite.com/

To avoid disappointment book early and let friends, colleagues know about the event

Inaugural meeting of the Belfast SQL Users Group on August 9th, 2012

A new SQL User Group has been set up in Northern Ireland and they will hold their inaugural meeting in Belfast on August 9th, 2012.

Network, learn, ask a question, meet other folk, get fed – these are all things that happen at user group events. These events are a really great opportunity to socialise in an informal learning experience – if you want your own exposure then come and do a 1 – 5 minute nugget in front of your peers.

Remember to tell your friends and the people you work with; make sure you register as soon as you can.

5.45 – 6:15pm Registration and networking
Meet and greet.

6:15 – 7:30pm Neil Hambly “SQL Server 2012 Memory Management”

SQL Server 2012 brings a wide range of changes from its previous versions – one key change is the way SQL Server memory is managed. With releases prior to SQL Server 2012, there were 2 memory managers, these have been merged into 1 with the latest version, and that’s just the start of the changes for SQL Server memory.

This session will dive into questions related to memory in SQL Server 2012

Session Takeaways:
• Explore SQL Server 2012’s new memory architecture and learn how to diagnose memory performance issues
• Gain insight on memory pressure issues
• Discover settings to adjust the memory configuration levels and their impact

Neil Hambly is a SQL veteran with over 13 years’ expertise in SQL Server from Version 6.5 right through to the very latest 2012 edition. Neil has held numerous DB roles at major organizations such as BBC, ABN AMRO, ACCENTURE, alongside a number of smaller companies, with prior roles as both a DBA and Developer, and currently is a Database Architect at MDSL.

Neil is an active speaker at conferences and user groups. He is SQL London PASS Chapter leader , he can often be seen presenting @ UK & International events.Database Architect, MDSL

7:30 – 7:50pm Break: Light refreshments
More time to network, ask questions and eat pizza…

7:50– 8:50pm Alex Whittles Mergetastic!

The more I use T-SQL’s Merge statement the more I love it. It’s fast, powerful, flexible and above all, it provides a world of opportunities to save a lot of time.

In this session we’ll look at how merge can be used to perform complex multi-stage tasks such as loading data into data warehouse dimensions, including the handling of type 0, 1 & 2 slowly changing dimensions.

I’ll present a summary of my MSc dissertation findings, comparing the performance of using T-SQL Merge against other more traditional methods of loading data warehouse dimensions in SSIS.

We’ll also look at how we can use simple metadata configuration and dynamic SQL to completely automate the generation of the merge statement, resulting in an incredibly simple meta-data configurable data warehouse platform.

Alex is the owner and lead consultant at Purple Frog, a SQL Server Business Intelligence consultancy in the UK. He specialises in dimensional data modelling, data warehouse design, ETL systems, OLAP cubes, MDX, PowerPivot and DAX. Alex is leader of the Birmingham PASS chapter and runs the Midlands SQL Relay events. He has just completed an MSc in Business Intelligence, writing a thesis on the performance characteristics of loading type 2 SCDs into a data warehouse. He’s also a regular speaker at SQLBits and at various SQL Server user groups and PASS chapters around the UK.8:50pm Wrap-up & Prize Giving

Time Starts (UK time) at 17:00 , Finishes 21:00
Cost Free
Organiser UK SQL Server User Group
Address Liberty Information Technology, Adelaide Exchange, 24- 26 Adelaide Street, Belfast, BT2 8GD
Registration http://sqlserverfaq.com/default.aspx?item=event&itemid=423

Dynamic code for efficient searching

Ian Meade presents “Dynamic code for efficient searching” to the SQL Server Ireland User Group this September.

When:                 September 4th,  2012 at 18.30

Where:                Microsoft Atrium, Carmanhall Road, Sandyford, Dublin 18

Cost:                    FREE Community Event

Registration is at http://www.mtug.ie/UserGroups/SQLServer/tabid/82/ctl/Details/Mid/413/ItemID/103/Default.aspx?ContainerSrc=[G]Containers/_default/No+Container

Ian will illustrate how multipurpose queries can lead to issues with scalability and performance. He will show a few alternatives that will work for smaller and simpler queries while highlighting their limitations for more complex queries. Then he works through building a dynamic search query to resolve the identified performance issues.
He touches on:
• Testing.
• Tuning each part of the query in isolation.
• Plan cache re-use
• Security – SQL injection attacks and authorisation / code signing.
• Possibly, mentioning greater re-use and extendibility.

Target audience:

Developers and DBA trying to resolve performance issues found when moving to medium or larger sized data sets.


Ian Meade has been working with SQL Server since 2001 and with a database / application developer for even longer. Lots of experience designing and implementing db solutions as well as fixing sick solutions. Holder of MCM SQL Server 2008, MCSD.NET and other certifications. Likes cheese.

%d bloggers like this: