Re: SA user has lost is SYSADMIN role
- From: r14edge <r14edge@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 26 Sep 2008 08:48:16 -0700
"Erland Sommarskog" wrote:
r14edge (r14edge@xxxxxxxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx
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,
.
- References:
- SA user has lost is SYSADMIN role
- From: r14edge
- Re: SA user has lost is SYSADMIN role
- From: Erland Sommarskog
- SA user has lost is SYSADMIN role
- Prev by Date: SQL Server 2005 and security patch issues?
- Next by Date: Re: SQL Server 2005 and security patch issues?
- Previous by thread: Re: SA user has lost is SYSADMIN role
- Next by thread: Which User account is used?
- Index(es):
Relevant Pages
|
