SEO

#1 (**permalink**) 02-29-2008, 05:16 AM

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?

#2 (**permalink**) 02-29-2008, 05:17 AM

Chris (cgrabowy@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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Lost sysadmin, but I still have it???