how to revive the administrator login in sql server 2008 R2?

hi,

by mistake i have deleted administrator user through Security--->Logins in SQL server Management Studio.in the beginning i used to login as Administor as windows authentication.now after deleting that user  i could not login  to db as Administrator as WINDOWS AUTHENTICATION .how to revive the administrator login in sql server 2008 R2?
LVL 20
chaitu chaituAsked:
Who is Participating?
 
Patrick BogersConnect With a Mentor Datacenter platform engineer LindowsCommented:
Hi

Under security -> login you should have (add) an entry for domain admins.
One created go to server roles and click all.

Now you should be able to login as domain admin, if you want local admin on that SQL server add the local admin.
0
 
chanderpal singh rathoreConnect With a Mentor Microsoft Exchange EngineerCommented:
Hi Sir,

Do you know the sa password? You could re-add it using the sa account or another account with admin rights

or can use the below link for solution of your problem

http://technet.microsoft.com/en-us/library/dd207004.aspx


Good Luck!!!!!!!!!!!
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

Actually it is pretty simple to correct IF SQL is operating in Mixed mode as described here.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
PadawanDBAOperational DBACommented:
Patricksr1972's post is exactly how to do it.  You don't need to have SQL Server in mixed mode, though, as long as the account you are logged in as is a member of the local admin group you can log in while SQL Server is in single user mode (you may have to run SSMS or SQLCMD as administrator to properly pass your token, by the way).

http://msdn.microsoft.com/en-us/library/dd207004.aspx
0
 
Anthony PerkinsCommented:
I suspect the author is asking because he does not have any login that belong to the sysadmin role including the sa login.  chaituu, is that correct?
0
 
chaitu chaituAuthor Commented:
as per the link i have completed these tasks and logged in as 'Administrator' but could n't perform the tasks.its saying "it is in single mode"

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


In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, find your instance of SQL Server. (The default instance of SQL Server includes (MSSQLSERVER) after the computer name. Named instances appear in upper case with the same name that they have in Registered Servers.) Right-click the instance of SQL Server, and then click Properties.
On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)
Note Note
For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Be careful not to change any of the existing parameters. At the very end, add a new parameter ;-m and then click OK. (That's a semi-colon then a dash then lower case letter m.)
Click OK, and after the message to restart, right-click your server name, and then click Restart.
After SQL Server has restarted your server will be in single-user mode. Make sure that that SQL Server Agent is not running. If started, it will take your only connection.
0
 
chaitu chaituAuthor Commented:
in the above link i didnt understand the step-8.whats is the meaning of  double-click your own login.i didnt find the Administrator login in Security-Logins..

8.On the Windows 8 start screen, right-click the icon for Management Studio. At the bottom of the screen, select Run as administrator. (This will pass your administrator credentials to SSMS.)
Note Note
For earlier versions of Windows, the Run as administrator option appears as a sub-menu.
In some configurations, SSMS will attempt to make several connections. Multiple connections will fail because SQL Server is in single-user mode. You can select one of the following actions to perform. Do one of the following.
Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click your own login. On the Server Roles page, select sysadmin, and then click OK.
0
 
chaitu chaituAuthor Commented:
now i am able to login as 'sa' user.but how to login as administrator as windows authentication?
0
 
PadawanDBAOperational DBACommented:
You will probably have to run SSMS as an administrator if you add the domain admins group in order to pass your admin token that will authenticate against that group.  I usually recommend creating a SQL sysadmin group and adding that group in instead.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.