Solved

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

Posted on 2014-01-10
9
379 Views
Last Modified: 2014-02-13
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?
0
Comment
Question by:chaitu chaitu
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 5

Assisted Solution

by:chanderpal singh rathore
chanderpal singh rathore earned 250 total points
ID: 39773028
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
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39773171
Hi

Actually it is pretty simple to correct IF SQL is operating in Mixed mode as described here.
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39773421
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39773875
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39775651
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
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39775658
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
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39776191
now i am able to login as 'sa' user.but how to login as administrator as windows authentication?
0
 
LVL 19

Accepted Solution

by:
Patricksr1972 earned 250 total points
ID: 39776209
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
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39776724
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now