Getting locked out of a SQL Server can happen in a number of ways. You could inherit a server that was managed by someone that left the company and only that person had System Admin rights for example. A recent encounter I had was were a database server was built and provisioned in one active directory domain and then moved and joined to another non trusted domain. All the accounts provisioned within the server include those for the DBA admins were basically useless since they couldn’t be authenticated. The built in SA account is locked down per policy so no one knows the password.
What do you do if you find that you are locked out of a SQL Server instance? The way I handle it is to start SQL Server in single user mode, launch SQLCMD and create a new user. I then still using SQLCMD grant the new user the system admin role. Once I have the new user created I restart SQL Server Service and log in using the new credentials. I can then clean up the instance by granting proper access.
If this was a newly inherited server I then take the time to do a health check of the instance to make sure that everything is to our standards. (Usually this means more work)
To start SQL Server in single user mode I open a command prompt as an administrator
I type the following minus the quotes (“ “)
“net stop mssqlserver” and press enter
“net start mssqlserver /m” and press enter
“sqlcmd” and press enter
“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ “ and press enter
“GO” and press enter
“sp_addsrvrolemember ‘security’, ‘sysadmin’ “ and press enter
“GO” and press enter
“quit” and press enter
“net stop mssqlserver” and press enter
“net start mssqlserver” and press enter
You have now stop and restarted mssql in single user mode, created a new login called ‘security’ and given the user ‘security’ system admin rights. You then stopped and started mssql to put it back in multi user mode. I have included a screen shot of me completing the task on a development workstation so you can see the syntax and steps.
At this point you can connect to the server with SQL Server Management Studio with the new user and reset the SA password and or provision the proper people access.
I have had the pleasure of taking my son with me on a number of trips where I was a presenting at a technical conference. My son loves to travel with me and to get to see exciting new places. I have seen a big change in him from the experiences of getting to meet my technical community and even some of my friends kids.
I must say that his first trip probably spoiled him a bit since it took 4 flights to get us to our destination and back home. I took him with me to Curacao which is a little Dutch island in the Caribbean. We enjoyed getting to drive around the island, snorkel, swim and enjoy some great food. I thought that would be his favorite trip.
A few months later I took him to New York City for a SQL Saturday. For this trip I contacted the event organizers and stated I would be bringing my son and asked if others would be bringing any children as well. My friend Robert Pearl (blog | twitter) said he was bringing his son and a couple of vendors would have their kids there too. The nice folks in NY reserved a room dedicated for the kids to hang out in and they had a great time playing games, watching movies and shopping at the vendor booths. I was able to take my son sight seeing all over Manhattan so he could see the buildings, statues, parks and monuments he only gets to see on TV. My son came alive on that trip.
The same year SQL Saturday Washington DC came up and I was selected to speak along with my friends Mike Walsh (blog | twitter), Andy Leonard (blog | twitter) and Brian Moran (blog | twitter). We all brought one of our little ones with us and turned the Friday before the event into a sight seeing day. Our kids bonded immediately and had a great but exhausting time together. We saw so many monuments during the day and took hundreds of photos while getting in a lot of exercise. The day of the event the kids played xbox 360 at the Microsoft campus further embedding their friendship. This event again made room for the kids to have a private place to hang out.
Again for SQL Saturday Atlanta a few of us brought our kids to the event and worked in some sight seeing at the aquarium. The kids got to reunite, the adults got to bond some more all the while getting to share our professional life with our kids.
My most recent event was taking my son to SQL in the City in Atlanta. We had a camping trip that started that evening so it just made sense to allow my son to skip a day of school so he could come with me to SQL in the City. It helped that he already had a Red Gate shirt from some SWAG from a previous trip and he also wanted to meet the Scary DBA. He wanted to use red gel to spike up his mohawk and became the mascot of Red Gate for the day. I even snapped a photo of him and Grant Fritchey (blog | twitter). When my son first got his new hair cut I snapped a picture of him and posted to facebook. He was trying his hardest to make his mean face and was wearing his Red Gate shirt. Everyone instinctively starting tagging Grant.
I urge any of you that have kids to let them tag along with you to a technical conference. My son has grown and developed so much from being involved in my technical community. He has friends all over the world now and can’t wait until he can start working in technology. All of his teachers have shared with me how much he has advanced from these opportunities. It has also been a great bonding opportunity for me and my son. He can’t wait for his next trip and I am counting down the days until I can start taking my middle daughter with me.
It was a huge honor to be chosen for the 2013 US tour of SQL in the City. Red Gate put on a phenomenal tour in 2012 hitting 6 major cities. This year they are bringing the event back. This year will bring Red Gate to Pasadena on October 9th, Atlanta on October 11th and Charlotte on October 14th. If you haven’t signed up already, you should really quick as with anything truly awesome, seating is limited. Find out more online http://sqlinthecity.red-gate.com/