Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-10
9
Medium Priority
?
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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