Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-10
9
Medium Priority
?
399 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 1000 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 23

Expert Comment

by:Patrick Bogers
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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 23

Accepted Solution

by:
Patrick Bogers earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

926 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