SA user has lost is SYSADMIN role

[r14edge]

Hello,

I'm having a problem has none of my admin login (Windows or SQL base)
are able to perform system administration like maintenance plan, add a login,
etc. I'm running a SQL 2000 SP4 on a W2K3 enterprise server. This is my
production server and my data manipulators are complaining about not been
able to perform certain admin task link shrinking a database, etc. I'm not a
DBA but this task fall upon me because I knew how to install a SQL server ...
Strangely, I have another SQL server 2000 with the same configuration and
that server has the same problem.

I've run EXEC sp_helpsrvrolemember 'sysadmin' and I got a error saying
"'sysadmin' is not a known fixed role". I run another query using the
following:

select loginname,sid
from master..syslogins
where sysadmin=1

And got all my admin account.

I found similar case like mine, but none had solution. I found a place where
the solution appear to be rebuilding the master database, but never knew if
that work. Also, someone suggested running 'dbcc checkcatalog <db_name>' but
don't say what to do next. I would like to think is related to a update
because it affect 2 servers at the same time, but I'm not sure since those 2
servers are not using automatic update.

So I would like to know if someone knows how to reassign sysadmin to my
admin logins easily (don't make me rebuild the master database ...). If there
is no easy way, well, just let me know what to do.

Thank you very much for your comments and suggestions to come,

Fred

[Erland Sommarskog]

Post by r14edge
I'm having a problem has none of my admin login (Windows or SQL base)
are able to perform system administration like maintenance plan, add a
login, etc. I'm running a SQL 2000 SP4 on a W2K3 enterprise server. This
is my production server and my data manipulators are complaining about
not been able to perform certain admin task link shrinking a database,
etc. I'm not a DBA but this task fall upon me because I knew how to
install a SQL server
...
Strangely, I have another SQL server 2000 with the same configuration and
that server has the same problem.
I've run EXEC sp_helpsrvrolemember 'sysadmin' and I got a error saying
"'sysadmin' is not a known fixed role". I run another query using the
select loginname,sid
from master..syslogins
where sysadmin=1
And got all my admin account.

Are you able to perform sysadmin tasks despite the missing sysadmin role?

What does this SELECT return:

select *from spt_values where type= 'SRV' and low = 0

I would expect it to return no rows at all, but if run it on a good server,
you will see all fixed server roles. If you remove "low = 0", you will
also see the tasks that a certain role can do.

spt_values is not a system table, in so far that sysobjects.type is U
for this table. But I don't know if SQL Server permits you to write to
this table. And one wonders that more could have gone wrong.

Post by r14edge
I found a place where the solution appear to be rebuilding the master
database, but never knew if that work.

I would expect that to work. But I will have to admit that I'm not
sure if you need to reinstall the service pack after this. I would
probably do it to be sure.

If you feel uncomfortable with the procedure, I recommend that you open
a case with Microsoft. It's not going to be precisely cheap, but it
may be cheaper in the end than if you fumble around on your own and mess
things up.

[r14edge]

Post by Erland Sommarskog

Post by r14edge
I'm having a problem has none of my admin login (Windows or SQL base)
are able to perform system administration like maintenance plan, add a
login, etc. I'm running a SQL 2000 SP4 on a W2K3 enterprise server. This
is my production server and my data manipulators are complaining about
not been able to perform certain admin task link shrinking a database,
etc. I'm not a DBA but this task fall upon me because I knew how to
install a SQL server
...
Strangely, I have another SQL server 2000 with the same configuration and
that server has the same problem.
I've run EXEC sp_helpsrvrolemember 'sysadmin' and I got a error saying
"'sysadmin' is not a known fixed role". I run another query using the
select loginname,sid
from master..syslogins
where sysadmin=1
And got all my admin account.

Are you able to perform sysadmin tasks despite the missing sysadmin role?
select *from spt_values where type= 'SRV' and low = 0
I would expect it to return no rows at all, but if run it on a good server,
you will see all fixed server roles. If you remove "low = 0", you will
also see the tasks that a certain role can do.
spt_values is not a system table, in so far that sysobjects.type is U
for this table. But I don't know if SQL Server permits you to write to
this table. And one wonders that more could have gone wrong.

Post by r14edge
I found a place where the solution appear to be rebuilding the master
database, but never knew if that work.

I would expect that to work. But I will have to admit that I'm not
sure if you need to reinstall the service pack after this. I would
probably do it to be sure.
If you feel uncomfortable with the procedure, I recommend that you open
a case with Microsoft. It's not going to be precisely cheap, but it
may be cheaper in the end than if you fumble around on your own and mess
things up.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Rebuilding my master database appears to be the solution. Coincidently, I
had the same problem with my test server and rebuilding the master database
did solve the problem on that server. Now, I will have to tackle the problem
on my production server ...

Thank you for your help Erland,