Home > General, T-sql > how to add yourself to sysadmin role

how to add yourself to sysadmin role

one of the best thing about attending SQL Saturday’s is the people you meet and tricks you learn .. (hmmm ok .. even the food included)

i come across similar webcast were i learned below 2 methods to add yourself  as sysadmin on any SQL instance ..

example : if the SQL installation was done by someone else and OS admins were never given sysadmins priviliges on SQL instance

so after you get yourself added to OS admin .. you can follow any of the below methods to achieve the same …

NOTE: both methods cause SQL restart .. so take a note of the same …

SQL Saturday 119 Lunch 017

SQL Saturday 119 Lunch 017 (Photo credit: Michael Kappel)

International Bibliography of Periodical Liter...

International Bibliography of Periodical Literature (Photo credit: Wikipedia)

 
Option 1: http://archive.msdn.microsoft.com/addselftosqlsysadmin/ (the logged in user gets added in sysadmin group)

download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..

double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance …

only enter the instance name (example for instance node-1\SQL2012_INST …. we will just enter SQL2012_INST .. not the network name)

below messages will appear in the cmd window .. (Note : it will cause SQL to restart as it puts instance in single-user mode and restarts it twice )

Adding ‘NODE-1\test’ to the ‘sysadmin’ role on SQL Server instance ‘SQL2012_INST
1’.
Verify the ‘MSSQL$SQL2012_INST1’ service exists …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping.
The SQL Server (SQL2012_INST1) service was stopped successfully.

Start the ‘MSSQL$SQL2012_INST1’ service in maintenance mode …
Add ‘NODE-1\test’ to the ‘sysadmin’ role …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping…
The SQL Server (SQL2012_INST1) service was stopped successfully.

The SQL Server (SQL2012_INST1) service is starting.
The SQL Server (SQL2012_INST1) service was started successfully.

‘NODE-1\test’ was successfully added to the ‘sysadmin’ role.
Press any key to continue . . .

Option: 2 http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

(you can explicitly choose which user/group is to be given sysadmin role, after you get yourself added in OS admin group)

Above is a manual method .. which has the same steps .. ie. Putting instance in single user and then adding a group or user to sysadmin role ..

enjoy 🙂

 

Advertisements
Categories: General, T-sql
  1. December 28, 2014 at 1:48 pm

    Additionally, research has proven this green helps in decreasing excess body fat.
    When in doubt, talk to a doctor or nutritionist about your
    weight loss plans. It is not impossible to have a simple,
    effective, and efficient weight loss plan that will finally give
    you the numbers you want while maintaining
    a healthy, overall life and eating normally.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: