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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chanderpal singh rathoreMicrosoft 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!!!!!!!!!!!
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

Actually it is pretty simple to correct IF SQL is operating in Mixed mode as described here.
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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?
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.
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.
chaitu chaituAuthor Commented:
now i am able to login as 'sa' user.but how to login as administrator as windows authentication?
Patrick BogersDatacenter 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.