SA user has lost is SYSADMIN role

SA user has lost is SYSADMIN role

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

date: Tue, 23 Sep 2008 10:46:00 -0700 author: r14edge

Re: SA user has lost is SYSADMIN role

r14edge (r14edge@discussions.microsoft.com) writes:
>     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.

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.

> 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.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
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

date: Tue, 23 Sep 2008 15:04:26 -0700 author: Erland Sommarskog

Re: SA user has lost is SYSADMIN role

"Erland Sommarskog" wrote:

> r14edge (r14edge@discussions.microsoft.com) writes:
> >     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.
> 
> 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.
> 
> > 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.
> 
> 
> 
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> 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,

date: Fri, 26 Sep 2008 08:48:16 -0700 author: r14edge