Fixed Database Roles have Disappeared From Enterprise Manager:Security:Server Roles

I'm afraid the answer to this question will fall into the "disaster
recovery" area...one in which I am woefully lacking.
OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
and one for production. I noticed a couple of weeks ago that the Fixed
Server Roles on the QA machine had suddenly disappeared. Everything seemed
to be performing ok, so I didn't give it much thought at the time...oops!
Now I am noticing that scheduled jobs have started failing on that machine
and I'm not sure why. (Side question: where exactly does one find the SQL
Server Logs that failures are supposedly written to?)
Where would these roles have gone to? Why would they have gone there? How
do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
reinstall SQL Server......while this isn't the production server - Thank
all that is holy! - the database backups on it have been failing...)
When I run sp_helprole I get:
public 0 0
db_owner 16384 0
db_accessadmin 16385 0
db_securityadmin 16386 0
db_ddladmin 16387 0
db_backupoperator 16389 0
db_datareader 16390 0
db_datawriter 16391 0
db_denydatareader 16392 0
db_denydatawriter 16393 0
When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
tab I get:
(0 row(s) affected)
...when I run sp_helpdbfixedrole on another server I get:
db_accessadmin DB Access Administrators
db_backupoperator DB Backup Operator
db_datareader DB Data Reader
db_datawriter DB Data Writer
db_ddladmin DB DDL Administrators
db_denydatareader DB Deny Data Reader
db_denydatawriter DB Deny Data Writer
db_owner DB Owners
db_securityadmin DB Security Administrators
when I run sp_helprolemember, I only get: db_owner dbo 0x01
when I run sp_helpdb, the db_size column for each database on this server is
null...
TIA,
Rox
> and I'm not sure why. (Side question: where exactly does one find the
SQL
> Server Logs that failures are supposedly written to?)
Please ignore the side question...I found them, but they don't seem to have
anything to do with this problem...
Rox
|||So what roles seemed to have disappeared? You say that the
fixed server roles disappeared but you are indicating issues
with and running stored procedures to look at the fixed
database roles?
What are the details for the errors on the jobs that are
failing? What are the error details for the backups that are
failing?
Have you run DBCCs on the databases?
-Sue
On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>I'm afraid the answer to this question will fall into the "disaster
>recovery" area...one in which I am woefully lacking.
>OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
>and one for production. I noticed a couple of weeks ago that the Fixed
>Server Roles on the QA machine had suddenly disappeared. Everything seemed
>to be performing ok, so I didn't give it much thought at the time...oops!
>Now I am noticing that scheduled jobs have started failing on that machine
>and I'm not sure why. (Side question: where exactly does one find the SQL
>Server Logs that failures are supposedly written to?)
>Where would these roles have gone to? Why would they have gone there? How
>do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
>reinstall SQL Server......while this isn't the production server - Thank
>all that is holy! - the database backups on it have been failing...)
>When I run sp_helprole I get:
>public 0 0
>db_owner 16384 0
>db_accessadmin 16385 0
>db_securityadmin 16386 0
>db_ddladmin 16387 0
>db_backupoperator 16389 0
>db_datareader 16390 0
>db_datawriter 16391 0
>db_denydatareader 16392 0
>db_denydatawriter 16393 0
>When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
>tab I get:
>(0 row(s) affected)
>...when I run sp_helpdbfixedrole on another server I get:
>db_accessadmin DB Access Administrators
>db_backupoperator DB Backup Operator
>db_datareader DB Data Reader
>db_datawriter DB Data Writer
>db_ddladmin DB DDL Administrators
>db_denydatareader DB Deny Data Reader
>db_denydatawriter DB Deny Data Writer
>db_owner DB Owners
>db_securityadmin DB Security Administrators
>when I run sp_helprolemember, I only get: db_owner dbo 0x01
>when I run sp_helpdb, the db_size column for each database on this server is
>null...
>TIA,
>Rox
>
|||> So what roles seemed to have disappeared? You say that the
> fixed server roles disappeared but you are indicating issues
> with and running stored procedures to look at the fixed
> database roles?
> What are the details for the errors on the jobs that are
> failing? What are the error details for the backups that are
> failing?
The fixed roles don't show up in Enterprise Manager or using
sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
doesn't have permissions. I managed to backup the most important DBs
manually (assuming that they are indeed good...I haven't tried restoring
them yet.

> Have you run DBCCs on the databases?
I've run DBCC CheckDatabase on all of the databases (including Master) with
no errors found.
Any ideas that you have would be appreciated.
Thanks,
Rox
[vbcol=seagreen]
> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
QA;[vbcol=seagreen]
seemed[vbcol=seagreen]
machine[vbcol=seagreen]
SQL[vbcol=seagreen]
How[vbcol=seagreen]
and[vbcol=seagreen]
Thank[vbcol=seagreen]
messages[vbcol=seagreen]
is
>
|||I have a feeling that it is time for MS support in your case. I've looked around and I didn't find any system
table where the fixed server roles are stored (but I've might have missed it, of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl...
> The fixed roles don't show up in Enterprise Manager or using
> sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> doesn't have permissions. I managed to backup the most important DBs
> manually (assuming that they are indeed good...I haven't tried restoring
> them yet.
>
> I've run DBCC CheckDatabase on all of the databases (including Master) with
> no errors found.
> Any ideas that you have would be appreciated.
> Thanks,
> Rox
>
> QA;
> seemed
> machine
> SQL
> How
> and
> Thank
> messages
> is
>
|||For server roles, try executing the following in Query
Analyzer and see if the roles are displayed:
select v1.name as 'Server Role',
v2.name as 'Role Description'
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number
For database roles, try executing the following in Query
analyzer and see if the roles are displayed:
select name as 'Role Name',
uid as 'Role ID',
isapprole as 'Is Application Role'
from sysusers
where issqlrole = 1 or isapprole = 1
-Sue
On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>The fixed roles don't show up in Enterprise Manager or using
>sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
>doesn't have permissions. I managed to backup the most important DBs
>manually (assuming that they are indeed good...I haven't tried restoring
>them yet.
>
>I've run DBCC CheckDatabase on all of the databases (including Master) with
>no errors found.
>Any ideas that you have would be appreciated.
>Thanks,
>Rox
>
>QA;
>seemed
>machine
>SQL
>How
>and
>Thank
>messages
>is
>
|||Seems like Sue might have nailed it. The procedure sp_helpdbfixedrole does indeed to a join against spt_values
so it is likely that non-existence of these rows in spt_values will give you the behavior you see...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uh7sJPFPEHA.3896@.TK2MSFTNGP12.phx.gbl...
> I have a feeling that it is time for MS support in your case. I've looked around and I didn't find any
system
> table where the fixed server roles are stored (but I've might have missed it, of course).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl...
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
> For server roles, try executing the following in Query
> Analyzer and see if the roles are displayed:
> select v1.name as 'Server Role',
> v2.name as 'Role Description'
> from master.dbo.spt_values v1, master.dbo.spt_values v2
> where v1.low = 0 and
> v1.type = 'SRV' and
> v2.low = -1 and
> v2.type = 'SRV' and
> v1.number = v2.number
This query returns 0 rows

> For database roles, try executing the following in Query
> analyzer and see if the roles are displayed:
> select name as 'Role Name',
> uid as 'Role ID',
> isapprole as 'Is Application Role'
> from sysusers
> where issqlrole = 1 or isapprole = 1
This query returns 10 rows as it should.
I guess the next step really is contacting Microsoft...or re-installing SQL
Server... Thanks for your help.
Rox
[vbcol=seagreen]
> -Sue
> On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
with[vbcol=seagreen]
for[vbcol=seagreen]
Fixed[vbcol=seagreen]
time...oops![vbcol=seagreen]
the[vbcol=seagreen]
drive[vbcol=seagreen]
server
>
|||Sue,
OK, I'm looking closer at master.dbo.spt_values...which is empty on that
server. The production server has lots of stuff in it...I don't think I
dare even look in this table on the production server...lol
So what is supposed to be in the spt_values table? Should I look in any
backups I have of Master to see if I can locate a time when this table
actually had values and try to restore them? Or will doing that bring the
server crashing to its knees? I've been looking in all of my SQL books
(haven't tried the web yet) and can't find anything about this table...can
you point me in the direction of more info?
Thanks,
Rox
"R Goodman" <bobnrox@.verizon.net> wrote in message
news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
> This query returns 0 rows
>
>
> This query returns 10 rows as it should.
> I guess the next step really is contacting Microsoft...or re-installing
SQL[vbcol=seagreen]
> Server... Thanks for your help.
> Rox
>
>
restoring[vbcol=seagreen]
> with
> for
> Fixed
> time...oops!
> the
there?
> drive
> server
>
|||I don't think spt_values is officially documented in any
Microsoft docs. It's mentioned in some articles and books -
you can find some information in this article:
http://www.winnetmag.com/SQLServer/A...8415/8415.html
and this FAQ post:
http://www.mssqlserver.com/faq/general-sptvalues.asp
I can't remember off the top of my head which books have
more information on it. I'd check those by Kalen Delaney and
Ken Henderson as they'd be most likely to have info on the
table.
But It's essentially just a large lookup table used by SQL
Server functions, stored procedures, etc. With no rows in
the table, you are a bit hosed.
You could do a restore from when you had data in the table.
-Sue
On Tue, 18 May 2004 15:51:13 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>Sue,
>OK, I'm looking closer at master.dbo.spt_values...which is empty on that
>server. The production server has lots of stuff in it...I don't think I
>dare even look in this table on the production server...lol
>So what is supposed to be in the spt_values table? Should I look in any
>backups I have of Master to see if I can locate a time when this table
>actually had values and try to restore them? Or will doing that bring the
>server crashing to its knees? I've been looking in all of my SQL books
>(haven't tried the web yet) and can't find anything about this table...can
>you point me in the direction of more info?
>Thanks,
>Rox
>
>
>"R Goodman" <bobnrox@.verizon.net> wrote in message
>news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
>SQL
>restoring
>there?
>