One trick that I have used numerous times and really like is to use a server alias when moving to new servers. For instance, we were moving numerous databases from a 2000 server named serversql0001. Since this server was so old, we had no real idea how many places or connections people may have used the alias, so we decided during the downtime to remove this alias and point it to the new server that we were moving the databases to. After we brought the new server up, we checked some of the software that was connecting to the databases, and as expected, everything was fine, with no need for connection changes.
Below are the steps you need to perform to grant SYSADMIN access to a user in SQL Server in case you are completely locked out.
1. Download PSexec to connect using SQL Server Management Studio using the NT Authority\System
Download PsTools from https://download.sysinternals.com/files/PSTools.zip
Unzip the content and copy PsExec.exe to C:\Windows\System32
2. Stop the SQL Server and SQL Server Agent services on the server.
3. Open a cmd prompt window as administrator and navigate to SQL Server’s Binn directory. You may need to adjust your path based on your install location.
ex. C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn
4. Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below. Had to add the location of the ERRORLOG
sqlservr -m -e C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG;
If it’s a named instance:
sqlservr -m -s -e C:\Program Files\Microsoft SQL Server\MSSQL11.SERVICECORE\MSSQL\Log\ERRORLOG;
After the SQL Server instance was started in single user mode, I was receiving logon errors form other users that were trying to logon from the application, when it was restarted in single user mode. I ignored the errors and moved on.
5. Execute PsExec
run cmd as administrator
PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”
The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM
6. Click Connect and then go in to Security > Logins and add your account as a sysadmin
7. restart the sql server services
- Must have Oracle client installed on computer
- Must have a tnsnames.ora file to connect to Oracle database
- Connecting to Oracle Database
A few of the OEM tables that I find interesting
I am thrilled to say that after 4 years of working with Microsoft SQL Server and the BI Tool Stack, I am going back to working with Oracle. I enjoyed working with Microsoft and getting to add another tool in my tool belt, but I am very excited about this new opportunity.
Hopefully this opportunity will lend itself to more blog posts.
Stay tuned for more details.
So far in my career I have been lucky enough to work with some really hot products. I started out of school as an Oracle DBA, while also performing SAP Basis duties. I did this for about 3 years, when I decided it was time to try something new. What I didn’t know at the time was that my Director did not want to lose me as a resource.
Our company was also starting to migrate our current Oracle and Cognos Data Warehouses to a Microsoft Data Warehouse. With my background as a DBA, they felt it was the next logical step in my career and they were right. I started in the group taking on SQL Server DBA tasks, while gaining the necessary skills with the Microsoft BI Stack. Looking back this was such an amazing opportunity. I was able to be an integral part of building the new warehouse from scratch. I had some brilliant co-workers, that were so willing to help me learn. I also had the opportunity to work with some really knowledgeable consultants from Pragmatic Works. A side effect to our company moving from the previous data warehouse to the new data warehouse was adopting Agile development. I had the opportunity to attend many BI related and Agile related trainings.
And now after leaving that company to join my current employer, I have continued to expand my BI skills. I am once again back to doing a lot of the SQL Server DBA tasks, but also learning more aspects of the process. I am still using Agile and the BI stack, but I’ve had the ability to bring my ideas to another warehouse conversion while also learning new approaches to migrations, development and architecture.
My future goals are to become MCSE Certified and work towards an Architect position. At some point, I would also like to become a Microsoft MVP.