We just moved from one domain to another.All the production servers(SQL Servers) were moved from the old to new domain.On the database server,out of the users registered,we know only one user login password(which does not have sysadin rights)
I open Enterprise Manager,Registered 2 sql server named instances.I want to backup the databases on the Server.But when i try to do that,it says that i do not have enough permissions(basically i do not have the sysadmin rights) to perform the operation.
Furthermore, i do not know the 'sa' password.I am unable to create new logins as well.I tried sp_password SP,but requires old password to be given.Is there a way to know the sa password or reset it without having sysadmin rights ?
Sorry for the delayed response.Was worked up with some other task.
The Builtin/Administrator group exists.The local Administrator password on the server machine was unknown and the password was reset by going to Control Panel - Administrator Tools- Computer Management-Local Users and Groups - Right click on Administrator user and change password.
Even when doing a RDP(remote desktop connection) to the server using Administrator login, and opening Enterprise Manager, and when i right click on the sa(login), there seems to be only 2 options : 1. New Login(which is disabled). 2.Help.
The option to reset password does not show up at all.I am using MS SQL Server 2000 on a Windows Server 2003 machine.
You do not need the sa account per se is what I think McCrowley was getting at.
If you are a Local Admin on the box, the group should give you sysadmin rights on the sql server unless someone has locked things down and if my very rusty DBA brain serves me correctly. They do not let me near production these days without an escort.
Yes i understand.But the local Administrator login password was also unknown and was reset to a new passsword.Even after logging into the box as Administrator, i am unable to do any task like for example,backup a database on the SQL Server. The error message is :
Server user 'machinename\Administrator' is not a valid user in database 'ReportsDB'.Backup Database terminated.
Can the sysadmin rights be restricted on Administrator also?or could the reset password on Administrator login on the server have denied the sysadmin rights?
I do not know what version you are working in, but you say you can log into the sql server buit not run a backup command. Have you tried adding the sql server user as a database user in your ReportsDB?
* 1. Download and install MS SQL Server Password Unlocker
* 2. Click the application icon to run it.
* 3. Click Open to import your master.mdf file. All the user names of your MS SQL Server will be displayed.
* 4. Select sa account, click Change password button to reset sa password.
* 5. Type a new password, and then click OK to finish your operation.
MS SQL Server Password Unlocker - A Highly Technical and Safe SQL password Recovery
Last edited by Shandelzare; 10-12-10 at 07:52.
Reason: delete the url
Also an excellent reason to lock down your SQL Server from any access but administrators. Granted it may be illegal, or that running this thing may break half a dozen clauses in that license agreement that you hit "I Agree" to on every installation.