By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Lost sysadmin, but I still have it???

P: n/a
SQL Server 2000 SP5a on Windows 2000 SP4

Friday morning we discovered that we no longer have sysadmin
privileges. We were able to query the syslogins table. In the output
we can clearly see that our accounts do have the sysadmin privileges,
since there is a 1 in that column. But yet we do not have sysadmin
privs!?!?!?!??!?!? Puzzling.

We are not able to get into the SA account, since no one knows the
password. But we are in BUILTIN/Administrators, and we have many SQL
Server authenticated accounts with sysadmin privs. But yet none of
them seem to have the privilege.

Saturday I was able to restart the instance (actually, several times
now), but that does not seem to resolve the problem. I have also
rebooted the server, which does not solve the problem.

The next option would be to restore Master from a few days ago, but
since I have no privileges I cant even do that!!!

Help? Ideas?

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Chris (cg******@gmail.com) writes:
SQL Server 2000 SP5a on Windows 2000 SP4

Friday morning we discovered that we no longer have sysadmin
privileges. We were able to query the syslogins table. In the output
we can clearly see that our accounts do have the sysadmin privileges,
since there is a 1 in that column. But yet we do not have sysadmin
privs!?!?!?!??!?!? Puzzling.
So how can you tell?
We are not able to get into the SA account, since no one knows the
password. But we are in BUILTIN/Administrators, and we have many SQL
Server authenticated accounts with sysadmin privs. But yet none of
them seem to have the privilege.
So when you are logged into SQL Server, what does

select is_member('BUILTIN\Administrators')

return?

If this indeed returns 1, then something is really broken, and I would
considering opening a case with Microsoft.
Saturday I was able to restart the instance (actually, several times
now), but that does not seem to resolve the problem. I have also
rebooted the server, which does not solve the problem.

The next option would be to restore Master from a few days ago, but
since I have no privileges I cant even do that!!!


And it might not help. What you can do is to install a second instance
on the same machine, this time keeping notes of the sa password. :-)
If you login with Windows Authentication to that second instance, and
you are not sysadmin, then there is something wacko outside SQL Server.
If you are sysadmin in the second instance, then there is a problem
with your main instance. At this stage you could retry restore the master
backup into this second instance.

But whatever you do, be very careful with these steps. Backup everything
on your machine, with SQL Server stopped before you play these games.
And a support case may be worth the money.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.