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 
