I would like to limit memory (RAM) usage for MSDE.
Since there is no Enterprise manager - is there another way how to do it?
(-> SQL command?!)
Thank You
PetrHi Petr
Please read about sp_configure in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Petr Stejskal" <petr.stejskal@.txn.cz> wrote in message
news:uoi1VuKnDHA.1884@.TK2MSFTNGP09.phx.gbl...
> I would like to limit memory (RAM) usage for MSDE.
> Since there is no Enterprise manager - is there another way how to do it?
> (-> SQL command?!)
> Thank You
> Petr
>
2012年3月11日星期日
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 an
d
>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 i
s
>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
> 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[vbcol=seagreen]
>|||I have a feeling that it is time for MS support in your case. I've looked ar
ound 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) wit
h
> 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 i
ndeed 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 a
nd 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@.TK
2MSFTNGP12.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
> -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[vbcol=seagreen]
>|||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
> Server... Thanks for your help.
> Rox
>
>
restoring[vbcol=seagreen]
> with
> for
> Fixed
> time...oops!
> the
there?[vbcol=seagreen]
> 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/.../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?
>
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 an
d
>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 i
s
>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
> 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[vbcol=seagreen]
>|||I have a feeling that it is time for MS support in your case. I've looked ar
ound 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) wit
h
> 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 i
ndeed 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 a
nd 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@.TK
2MSFTNGP12.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
> -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[vbcol=seagreen]
>|||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
> Server... Thanks for your help.
> Rox
>
>
restoring[vbcol=seagreen]
> with
> for
> Fixed
> time...oops!
> the
there?[vbcol=seagreen]
> 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/.../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?
>
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?
>
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?
>
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
> 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
> >
> >
>|||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...
> > 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
>
> > 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
> > >
> > >
> >
>|||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:
>> 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
>
>> 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
>> >
>> >
>|||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...
> > > 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
> >
> >
> > > 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
> > > >
> > > >
> > >
> >
> >
>|||"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
> -Sue
> On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
> >> 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
> >
> >
> >> 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
> >> >
> >> >
> >>
> >
>|||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...
> > 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
>
>
> >
> > -Sue
> >
> > On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> > <bobnrox@.verizon.net> wrote:
> >
> > >> 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
> > >
> > >
> > >> 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
> > >> >
> > >> >
> > >>
> > >
> >
>|||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/Article/ArticleID/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...
>> "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
>>
>>
>> >
>> > -Sue
>> >
>> > On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
>> > <bobnrox@.verizon.net> wrote:
>> >
>> > >> 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
>> > >
>> > >
>> > >> 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
>> > >> >
>> > >> >
>> > >>
>> > >
>> >
>>
>
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
> 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
> >
> >
>|||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...
> > 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
>
> > 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
> > >
> > >
> >
>|||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:
>> 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
>
>> 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
>> >
>> >
>|||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...
> > > 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
> >
> >
> > > 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
> > > >
> > > >
> > >
> >
> >
>|||"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
> -Sue
> On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
> >> 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
> >
> >
> >> 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
> >> >
> >> >
> >>
> >
>|||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...
> > 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
>
>
> >
> > -Sue
> >
> > On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> > <bobnrox@.verizon.net> wrote:
> >
> > >> 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
> > >
> > >
> > >> 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
> > >> >
> > >> >
> > >>
> > >
> >
>|||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/Article/ArticleID/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...
>> "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
>>
>>
>> >
>> > -Sue
>> >
>> > On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
>> > <bobnrox@.verizon.net> wrote:
>> >
>> > >> 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
>> > >
>> > >
>> > >> 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
>> > >> >
>> > >> >
>> > >>
>> > >
>> >
>>
>
2012年3月7日星期三
first setup
I setup yesterday sql server 2008 on VPC for studio Orcas. Everything was smooth, however when I finished setup I did not see familiar enterprise management studio which allows to manage database files. what I can do?
Did you install management studio? During install there's a question about components you wish to install, you have to mark "Workstation components, Books Online and development tools". If you click the "Advanced" button you'll see that Management Studio is located in Client Components -> Management Tools branch.
Actually, I had a similar problem. After full install (including workstation components) Management Studio wasn't in my program list, no idea why. I started the setup program again, chose to install workstation components again and now it works perfectly.
|||i did exactly as you wrote. I nade setup twice. And third time it did not allow me to pass by because everything is installed. However the only thing that I have installed is configuration tools.|||I'm running into a similar problem. I had SQL Server 2005 w/ SQL Server Manager loaded on XP SP2. I uninstalled SQL Server 2005, and loaded SQL Server 2008. However the SQL Server Mgr application didn't load. I attempted to reinstall SQL Server 2008, but it says they're no components that have changed...which indicates that the SQL Server 2008 Manager must be loaded already (but I don't see it)?
2012年2月26日星期日
first setup
I setup yesterday sql server 2008 on VPC for studio Orcas. Everything was smooth, however when I finished setup I did not see familiar enterprise management studio which allows to manage database files. what I can do?
Did you install management studio? During install there's a question about components you wish to install, you have to mark "Workstation components, Books Online and development tools". If you click the "Advanced" button you'll see that Management Studio is located in Client Components -> Management Tools branch.
Actually, I had a similar problem. After full install (including workstation components) Management Studio wasn't in my program list, no idea why. I started the setup program again, chose to install workstation components again and now it works perfectly.
|||i did exactly as you wrote. I nade setup twice. And third time it did not allow me to pass by because everything is installed. However the only thing that I have installed is configuration tools.|||I'm running into a similar problem. I had SQL Server 2005 w/ SQL Server Manager loaded on XP SP2. I uninstalled SQL Server 2005, and loaded SQL Server 2008. However the SQL Server Mgr application didn't load. I attempted to reinstall SQL Server 2008, but it says they're no components that have changed...which indicates that the SQL Server 2008 Manager must be loaded already (but I don't see it)?
2012年2月24日星期五
Firewall Problem
I have opened port 1433 on my firewall, but I am still not able to get
remote clients to connect via query analyser, or enterprise manager.
Are there other ports required to be opened ?Have you checked your remote clients to make sure TCP/IP sockets is the
default protocol? You may have to go SQL Client Network Utility or run
through a SQL Server ODBC System DSN (Control Panel | ODBC Data Sources) and
verify that on the user login credential page that the Client Configuration
that it says TCP/IP sockets.
I did look in SQL Server books online in the Network Libraries topic, and
read this
SQL Server uses UDP port 1434 to establish connections from SQL Server 2000
clients. This socket number is also reserved for SQL Server by Internet
Assigned Number Authority (IANA).
You can download SQL Server books online for free from MS
http://www.microsoft.com/sql/techin.../2000/books.asp
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"AussieRules" <someone@.nowhere.com> wrote in message
news:upbiLWY6DHA.3288@.TK2MSFTNGP11.phx.gbl...
remote clients to connect via query analyser, or enterprise manager.
Are there other ports required to be opened ?Have you checked your remote clients to make sure TCP/IP sockets is the
default protocol? You may have to go SQL Client Network Utility or run
through a SQL Server ODBC System DSN (Control Panel | ODBC Data Sources) and
verify that on the user login credential page that the Client Configuration
that it says TCP/IP sockets.
I did look in SQL Server books online in the Network Libraries topic, and
read this
SQL Server uses UDP port 1434 to establish connections from SQL Server 2000
clients. This socket number is also reserved for SQL Server by Internet
Assigned Number Authority (IANA).
You can download SQL Server books online for free from MS
http://www.microsoft.com/sql/techin.../2000/books.asp
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"AussieRules" <someone@.nowhere.com> wrote in message
news:upbiLWY6DHA.3288@.TK2MSFTNGP11.phx.gbl...
quote:
> I have opened port 1433 on my firewall, but I am still not able to get
> remote clients to connect via query analyser, or enterprise manager.
> Are there other ports required to be opened ?
>
>
Firewall Port 1443
I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my Remote database. In Enterprise Manager it won't resolve listing the server and it's tables. I can register and get a connection though. My host said that if I am behind the firewal
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
Stickfigure
Because everyone likes Figures.
can you connect using query analyser? Or osql?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||If it is a firewall issue, and if port 1433 is blocked then the chances are
that ports 8080 and 1222 are blocked also. You might need to take this up
with your firewall administrator.
"Stickfigure" <Stickfigure@.discussions.microsoft.com> wrote in message
news:792F6F89-F318-4E47-99FA-189E9D082BDD@.microsoft.com...
>I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my
>Remote database. In Enterprise Manager it won't resolve listing the server
>and it's tables. I can register and get a connection though. My host said
>that if I am behind the firewall, that it blocks Port 1433. I tried
>switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
> Stickfigure
> Because everyone likes Figures.
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
Stickfigure
Because everyone likes Figures.
can you connect using query analyser? Or osql?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||If it is a firewall issue, and if port 1433 is blocked then the chances are
that ports 8080 and 1222 are blocked also. You might need to take this up
with your firewall administrator.
"Stickfigure" <Stickfigure@.discussions.microsoft.com> wrote in message
news:792F6F89-F318-4E47-99FA-189E9D082BDD@.microsoft.com...
>I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my
>Remote database. In Enterprise Manager it won't resolve listing the server
>and it's tables. I can register and get a connection though. My host said
>that if I am behind the firewall, that it blocks Port 1433. I tried
>switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
> Stickfigure
> Because everyone likes Figures.
Firewall Port 1443
I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my Remote database. In Enterprise Manager it won't resolve listing the server and it's tables. I can register and get a connection though. My host said that if I am behind the firewal
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
Stickfigure
Can you connect through query analyser to another machine?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
Stickfigure
Can you connect through query analyser to another machine?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Firewall Port 1443
I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my R
emote database. In Enterprise Manager it won't resolve listing the server an
d it's tables. I can register and get a connection though. My host said that
if I am behind the firewal
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. St
ill no luck. I am stuck. Any ideas?
StickfigureCan you connect through query analyser to another machine?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
emote database. In Enterprise Manager it won't resolve listing the server an
d it's tables. I can register and get a connection though. My host said that
if I am behind the firewal
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. St
ill no luck. I am stuck. Any ideas?
StickfigureCan you connect through query analyser to another machine?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Firewall Port 1443
I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my R
emote database. In Enterprise Manager it won't resolve listing the server an
d it's tables. I can register and get a connection though. My host said that
if I am behind the firewal
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. St
ill no luck. I am stuck. Any ideas?
Stickfigure
Because everyone likes Figures.can you connect using query analyser? Or osql?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||If it is a firewall issue, and if port 1433 is blocked then the chances are
that ports 8080 and 1222 are blocked also. You might need to take this up
with your firewall administrator.
"Stickfigure" <Stickfigure@.discussions.microsoft.com> wrote in message
news:792F6F89-F318-4E47-99FA-189E9D082BDD@.microsoft.com...
>I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my
>Remote database. In Enterprise Manager it won't resolve listing the server
>and it's tables. I can register and get a connection though. My host said
>that if I am behind the firewall, that it blocks Port 1433. I tried
>switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
> Stickfigure
> Because everyone likes Figures.
emote database. In Enterprise Manager it won't resolve listing the server an
d it's tables. I can register and get a connection though. My host said that
if I am behind the firewal
l, that it blocks Port 1433. I tried switching the Port to 8080 and 1222. St
ill no luck. I am stuck. Any ideas?
Stickfigure
Because everyone likes Figures.can you connect using query analyser? Or osql?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||If it is a firewall issue, and if port 1433 is blocked then the chances are
that ports 8080 and 1222 are blocked also. You might need to take this up
with your firewall administrator.
"Stickfigure" <Stickfigure@.discussions.microsoft.com> wrote in message
news:792F6F89-F318-4E47-99FA-189E9D082BDD@.microsoft.com...
>I installed SQL Server 2000 Enterprise Trial. I am trying to connect to my
>Remote database. In Enterprise Manager it won't resolve listing the server
>and it's tables. I can register and get a connection though. My host said
>that if I am behind the firewall, that it blocks Port 1433. I tried
>switching the Port to 8080 and 1222. Still no luck. I am stuck. Any ideas?
> Stickfigure
> Because everyone likes Figures.
2012年2月19日星期日
firehouse mode
SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>> Aaron's suggestion did not work.
What does "did not work" mean?
http://www.aspfaq.com/
(Reverse address to reply.)
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>> Aaron's suggestion did not work.
What does "did not work" mean?
http://www.aspfaq.com/
(Reverse address to reply.)
firehouse mode
SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>
> Aaron's suggestion did not work.
What does "did not work" mean?
http://www.aspfaq.com/
(Reverse address to reply.)
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>
> Aaron's suggestion did not work.
What does "did not work" mean?
http://www.aspfaq.com/
(Reverse address to reply.)
Firehose mode
Hi
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
It means that you should open a Query Analyzer window and write an UPDATE
statement.
I don't know exactly what it means. But I usualay solve this problem opening
not whole table but only e.q. TOP 10
>
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://support.microsoft.com/kb/286199
On Jul 23, 8:55 pm, "Oscar Santiesteban"
<o_santieste...@.bellsouth.netwrote:
>
>
>
>
>
>
- Show quoted text -|||Jason Lepack (jlepack@.gmail.com) writes:
Ah, that was a very special situation. Thanks for the link, Jason.
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
Can anyone explain what this is and what it means ?
Thanks
David GreenbergDavid Greenberg (davidgr@.iba.org.il) writes:
Quote:
Originally Posted by
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Can anyone explain what this is and what it means ?
It means that you should open a Query Analyzer window and write an UPDATE
statement.
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "David Greenberg" <davidgr@.iba.org.ilnapisal w wiadomosci
news:469C9971.7050205@.iba.org.il...
Quote:
Originally Posted by
Hi
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Can anyone explain what this is and what it means ?
I don't know exactly what it means. But I usualay solve this problem opening
not whole table but only e.q. TOP 10
br
Bober|||Erland,
Doesn't FIREHOSE mode mean that EM has read the records in a "forward only
cursor" and has no means of updating? Hence the name "firehouse" which is a
rapid stream of data only going 1 way.
Oscar
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99707AD4B7BCCYazorman@.127.0.0.1...
Quote:
Originally Posted by
David Greenberg (davidgr@.iba.org.il) writes:
Quote:
Originally Posted by
>I'm in Enterprise Manager and have made a change to a row.
>When I try to update I get a message that says:
>Transaction cannot start, Firehose mode"
>>
>Can anyone explain what this is and what it means ?
>
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://support.microsoft.com/kb/286199
On Jul 23, 8:55 pm, "Oscar Santiesteban"
<o_santieste...@.bellsouth.netwrote:
Quote:
Originally Posted by
Erland,
>
Doesn't FIREHOSE mode mean that EM has read the records in a "forward only
cursor" and has no means of updating? Hence the name "firehouse" which is a
rapid stream of data only going 1 way.
>
Oscar
>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns99707AD4B7BCCYazorman@.127.0.0.1...
>
>
>
Quote:
Originally Posted by
David Greenberg (davi...@.iba.org.il) writes:
Quote:
Originally Posted by
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Can anyone explain what this is and what it means ?
>
Quote:
Originally Posted by
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
Quote:
Originally Posted by
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
Quote:
Originally Posted by
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Quote:
Originally Posted by
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -
>
- Show quoted text -|||Jason Lepack (jlepack@.gmail.com) writes:
Quote:
Originally Posted by
http://support.microsoft.com/kb/286199
Ah, that was a very special situation. Thanks for the link, Jason.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
订阅：
博文 (Atom)