MSDE 2000 mulituser on a single computer

The installation works fine, when I restarted the machine and login as an
administrator it works fine...
But when I logon as a normal user (whitout administravie rights) its no fun
anymore.
The "SQL Server Service Manager" is blank (no server or services), but I can
fill the fields with values and the service is started but they disappear
when i restart the machine, and the MSSQLSERVER service IS started!?!
But when I enable NetworkProtocols (with "Svrnetcn.exe") it works fine for
all...
Is there any other way to solve the problem?
Where can I find Microsofts documentation about this?
/Erik Fahlstrm
hi Erik,
"Erik Fahlstrm" <erik(dot)fahlstrom(at-sign)intentia(dot)se> ha scritto nel
messaggio news:%23mA1CJ3MEHA.684@.TK2MSFTNGP09.phx.gbl...
> The installation works fine, when I restarted the machine and login as an
> administrator it works fine...
> But when I logon as a normal user (whitout administravie rights) its no
fun
> anymore.
> The "SQL Server Service Manager" is blank (no server or services), but I
can
> fill the fields with values and the service is started but they disappear
> when i restart the machine, and the MSSQLSERVER service IS started!?!
> But when I enable NetworkProtocols (with "Svrnetcn.exe") it works fine for
> all...
> Is there any other way to solve the problem?
> Where can I find Microsofts documentation about this?
> /Erik Fahlstrm
this is a known issue as
http://support.microsoft.com/default...b;EN-US;814132 reports..
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 does not run under Power User

Hi All,
I installed MSDE 2000 from my Office XP CD while logged in a
Administrator. I then applied SP 3 and HotPatch 815495. While logged
in as Administrator everything works fine.
However, when I log in as a Power User I have problems. MSDE seems to
load during the login process as I can see the icon in the taskbar.
But, when I try to load any program I get the MSDE 2000 installer
invoked. It goes through severl stages and I can see the progress bar
moving to the right. Once it gets all the way to the right it then
reverses itself and moves all the way back to the left. After the
installer dialogs are gone, showing no messages at all, the icon in
the taskbar is also gone.
How do I get MSDE 2000 to run correctly while logged in as a Power
User?
Thanks for any help,
Charles
hi Charles,
"Charles E Finkenbiner" <CharlesEF@.MailandNews.Com> ha scritto nel messaggio
news:d55b48cb.0409190449.732e1bb1@.posting.google.c om...
> Hi All,
> I installed MSDE 2000 from my Office XP CD while logged in a
> Administrator. I then applied SP 3 and HotPatch 815495. While logged
> in as Administrator everything works fine.
> However, when I log in as a Power User I have problems. MSDE seems to
> load during the login process as I can see the icon in the taskbar.
> But, when I try to load any program I get the MSDE 2000 installer
> invoked. It goes through severl stages and I can see the progress bar
> moving to the right. Once it gets all the way to the right it then
> reverses itself and moves all the way back to the left. After the
> installer dialogs are gone, showing no messages at all, the icon in
> the taskbar is also gone.
> How do I get MSDE 2000 to run correctly while logged in as a Power
> User?
I never used and installed the MSDE 2000 release that comes with Office XP,
but I'm quite sure it's the same package available for download from
Microsoft Web Site...
and MSDE has no installer if not the seutp.exe boostrap installer you can
usually find in the \MSDE folder of the Office CD, so no "repair" Windows
Intaller feature should be present ...
and I usually run MSDE when logged as power user or standard user.. with no
problem at all
the SQL Service is usually run in the contest of a local admin [or
LocalSystem, sometime :-( ]..
please verify MSDE is correctly running before executing your own
application(s) to verify it's not an MSDE problem...
open the control panel-> services ->MSSQLSSERVER and verify it's correctly
running...
execute oSQL.exe to connect or your preferred management application...
open with notepad the \Program Files\Microsoft SQL Server\MSSQL\Log\ERRORLOG
text file and search for errors, open the event viewer and search for SQL
Server related troubles...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
While logged in as a Power User services show MSSQLSERVER as started.
ERRORLOG shows errors binding to port 1433 but at the end it states
that SQLSERVER is listening on port 1433 and ready for connections.
The application event log show many MsiInstaller errors regarding
MSSQLSERVER. Registry keys missing, no permission to write to a file
in the Install directory, configuration failed.
I am just going to uninstall it and try over, again. I will post back
my results.
Thanks for your help,
Charles
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message news:<2r5mthF12sg5rU1@.uni-berlin.de>...
> hi Charles,
> "Charles E Finkenbiner" <CharlesEF@.MailandNews.Com> ha scritto nel messaggio
> news:d55b48cb.0409190449.732e1bb1@.posting.google.c om...
> I never used and installed the MSDE 2000 release that comes with Office XP,
> but I'm quite sure it's the same package available for download from
> Microsoft Web Site...
> and MSDE has no installer if not the seutp.exe boostrap installer you can
> usually find in the \MSDE folder of the Office CD, so no "repair" Windows
> Intaller feature should be present ...
> and I usually run MSDE when logged as power user or standard user.. with no
> problem at all
> the SQL Service is usually run in the contest of a local admin [or
> LocalSystem, sometime :-( ]..
> please verify MSDE is correctly running before executing your own
> application(s) to verify it's not an MSDE problem...
> open the control panel-> services ->MSSQLSSERVER and verify it's correctly
> running...
> execute oSQL.exe to connect or your preferred management application...
> open with notepad the \Program Files\Microsoft SQL Server\MSSQL\Log\ERRORLOG
> text file and search for errors, open the event viewer and search for SQL
> Server related troubles...
|||Hi,
OK, I give up. After trying many times I can not get SQL Desktop
Server installed to run under a Power User account. I have no trouble
running it as a member of the Administrator group. It has gotten to
the point that MsiInstaller locks up during the Power User
installation.
I have tried to install it while logged in as a Power User but the
installer states I do not have rights to install and exits.
Any other ideas you can supply?
Thanks for any help,
Charles
CharlesEF@.MailandNews.Com (Charles E Finkenbiner) wrote in message news:<d55b48cb.0409200014.30c832e6@.posting.google. com>...[vbcol=seagreen]
> Hi Andrea,
> While logged in as a Power User services show MSSQLSERVER as started.
> ERRORLOG shows errors binding to port 1433 but at the end it states
> that SQLSERVER is listening on port 1433 and ready for connections.
> The application event log show many MsiInstaller errors regarding
> MSSQLSERVER. Registry keys missing, no permission to write to a file
> in the Install directory, configuration failed.
> I am just going to uninstall it and try over, again. I will post back
> my results.
>
> Thanks for your help,
> Charles
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message news:<2r5mthF12sg5rU1@.uni-berlin.de>...
|||Hi,
Ok, I have some more information to pass along.
As I said before, when I login as Administrator (or any user in the
Administrator group) I have no problems what so ever. When I login as
a Power User MSDE appears to load without problems and I can see the
SQLManager icon in the taskbar. But, when I load a certain program
the MSDE installer kicks in and locks up (I have to End Task) and the
SQLManager icon disappears from the taskbar.
After the End Task I can still run SQL queries and they still work.
So, it appears that MSDE is still working even though the icon
disappears. The MSDE installer kicks in every time I load a certain
program (that I know of, so far) then locks up.
Is there a way for me to disable the MSDE installer? Some entry in
the Registry, perhaps?
Thanks for any help,
Charles
CharlesEF@.MailandNews.Com (Charles E Finkenbiner) wrote in message news:<d55b48cb.0409201329.f807f2b@.posting.google.c om>...[vbcol=seagreen]
> Hi,
> OK, I give up. After trying many times I can not get SQL Desktop
> Server installed to run under a Power User account. I have no trouble
> running it as a member of the Administrator group. It has gotten to
> the point that MsiInstaller locks up during the Power User
> installation.
> I have tried to install it while logged in as a Power User but the
> installer states I do not have rights to install and exits.
> Any other ideas you can supply?
>
> Thanks for any help,
> Charles
> CharlesEF@.MailandNews.Com (Charles E Finkenbiner) wrote in message news:<d55b48cb.0409200014.30c832e6@.posting.google. com>...
|||Hi,
Is there a way for me to disable the MSDE installer? Some entry in
the Registry, perhaps?
Thanks for any help,
Charles
|||CharlesEF@.MailandNews.Com (Charles E Finkenbiner) wrote in message news:<d55b48cb.0409221950.22e79cc4@.posting.google. com>...
> Hi,
> Is there a way for me to disable the MSDE installer? Some entry in
> the Registry, perhaps?
>
> Thanks for any help,
> Charles
Hi All,
I fixed my problem. I had to change permissions for the MSSQLSERVER
tree in the registry. Right or wrong, it worked for me. No more
stupid MSI installer.
Charles

MSDE + Restricted Accounts

I appologize if this is in the wrong place. I have MSDE 2000 installed on a
machine to be used only by the local user. It is using mixed-mode authentic
ation. The machine is running windows 2000. When I login as a Restricted Use
r that user can't start the
MQSQL Server service. An Error 5 (Access Denied) is reported. I read in the
KB before that MSDE and even accesss 2000 can not run on restricted user acc
ounts by design. I would just like a link to that KB article. I have searche
d all day for it and I can
't seem to find the right combination of keywords. Thanks so much. Most of t
he KB articles seem to be only for XP, .NET and Office 2003.The problem is that only local admins have permission to stop and start
services.
You should be able to configure the MSSQL service to start automatically,
using a limited user account.
But, as a limited user logging onto a machine, you won't have the ability
to stop and start services.
Here's a referenced to the startup account permissions.
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

msde # of users allowed.

i have a questions concerning msde 2000.
1- how many user can access an msde database at one time?
(some resources say 5 some sau unlimited.)I believe it is 10|||You can find complete information here:
MSDE 2000 (http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp)
here is an excerpt from this page:
Performance
MSDE 2000 is a local data engine that can be shared. It has a managed concurrency workload governor that limits up to five concurrent batch workloads for optimal performance. Commands and log entries are available to monitor instances where more than five workloads are executed concurrently, a situation that can cause slower performance even on well-tuned systems. As more batch workloads are submitted beyond the five-workload limit, the concurrency governor continues to slow down the system. These workloads are not dropped or lost; they are still processed, but in an increasingly degraded performance mode. If your solution must support more than five concurrent workloads, it is highly recommended that you migrate to SQL Server 2000 or SQL Server 2000 Enterprise Edition for optimal performance at this higher level of scalability.

Hope this helps|||MSDE 1.0 (SQL 7) had a governor on the # of connections.

MSDE 2.0 (SQL 2k) just has a governor on concurrent processes.

so with MSDE 2.0, you can have 25 connections that stay open all the time-- and it works like a charm.

with MSDE 1.0, you would be going really slowly.

if you're asking 'is this the right solution for me'

i think that the answer is yes.

but you'll have to give us more information about your project before we can come to the correct diagnosis.

MSDE - Grant Permission?

Hi all,
If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.
How can I do? Would you mind to give me some examples?
Many thanks.Hi all,

If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.

How can I do? Would you mind to give me some examples?

Many thanks.

Check sp_addrolemember , Grant, sp_addlogin in Bol its been explained very well with good examples.|||Yes, I used sp_addrolemember, grant , etc.

The following are my steps:
1. use "sa" login to "master"
2. exec sp_addlogin "abc", "abc", "master", null, null, null
go
3. exec sp_adduser "abc", "abc1", "db_owner"
4. exec sp_addrolemember "db_accessadmin" , "abc1"
go
5. exec sp_addrolemember "db_securityadmin" , "abc1"
go
6. exec sp_addrolemember "db_ddladmin" , "abc1"
go
7. exec sp_addrolemember "db_backupoperator" , "abc1"
go
8. exec sp_addrolemember "db_datareader" , "abc1"
go
9. exec sp_addrolemember "db_datawriter" , "abc1"
go
9.5. grant all on sp_addlogin to abc1
go
10. use "abc" login into "master"
11. exec sp_addlogin "bb", "bbpassword", "master", null, null, null
go

Msg 15247, Level 16, State 1, Server MIS-TOMMY\AIAB, Procedure sp_addlogin, Line
17
User does not have permission to perform this action.

How can I solve this?

Many thanks.|||Yes, I used sp_addrolemember, grant , etc.

The following are my steps:
1. use "sa" login to "master"
2. exec sp_addlogin "abc", "abc", "master", null, null, null
go
3. exec sp_adduser "abc", "abc1", "db_owner"
4. exec sp_addrolemember "db_accessadmin" , "abc1"
go
5. exec sp_addrolemember "db_securityadmin" , "abc1"
go
6. exec sp_addrolemember "db_ddladmin" , "abc1"
go
7. exec sp_addrolemember "db_backupoperator" , "abc1"
go
8. exec sp_addrolemember "db_datareader" , "abc1"
go
9. exec sp_addrolemember "db_datawriter" , "abc1"
go
9.5. grant all on sp_addlogin to abc1
go
10. use "abc" login into "master"
11. exec sp_addlogin "bb", "bbpassword", "master", null, null, null
go

Msg 15247, Level 16, State 1, Server MIS-TOMMY\AIAB, Procedure sp_addlogin, Line
17
User does not have permission to perform this action.

How can I solve this?

Many thanks.

Just add two lines before line 10
exec sp_addsrvrolemember N'abc', sysadmin
go
EXEC sp_grantdbaccess N'abc', N'abc1'
go

I hope now it would be ok.
Joydeep :cool:

MSDE - ADP Question

Hi,

After upsizing on MSDE, I created ADP on my terminal.

When the created ADP I need to copy to another user to get connected to MSDE hosted on my pc I followed these steps.

First I copied ADP to the user's pc and

1. Opened Administration Tools on user's pc
2. Clicked twice Data Source (ODBC) icon
3. Selected System DSN and clicked Add
4. Selected SQL Server (Last item) and Finish
5. Given new connection name (any name) in the field 'Name'. Description left empty. In Servaer, typed server name.
6. In the next page, I selected SQL Server Authentication and...

Here is my question:

Should I use at this step System Administrator's login id and password ?

OR

Should I use user's pc id and password ?

I tried both way. But login fails.

Where might have gone wrong? BTW, this the first user I trying on.

Any help would be highly appreviated.

With kind regards,

Ashfaquecreate a sql login in our MSDE using sp_addlogin and then use sp_adduser to grant them specific db access. see details in BOL. if you are using Wndows XP SP2 you may have to create an exception in the personal firewall r drop it altogether.|||Thanks Thrasymachus,

I would appreciate it very much if you could little bit in detail as I am new bee for this area.

With kind regards,

Ashfaque|||Do you have Books Online? If so (it's included free with SQL Server), open it up and go to the Index tab. Type in "logins-SQL Server" and start reading. It will give you more than enough information to accomplish this task. You can also type in the stored procedures he gave you to get syntax and usage information.|||oh why use the reference material. I drove for a couple of years without a license when I was a kid.|||Hi Guys,

No, I don't have Books Online..

Justi moving around with the help of you people...

I attached jpg image to give you a clear pic to know whats wrong..

With kind regards,

Ashfaque|||Hello Gentlemen,

I am still waiting for your valuable response..

With kind regards,

Ashfaque

MSDB Table User Permissions

Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like...
dts_admin: <dts_admin description>

Thanks in advance.
-Kyle

Unfortunately msdb is being used by a number of different SQL Server services (built on top of SQL Server Engine, but shipped as part of SQL Server itself), and each type of service documents their own set of roles and usage (i.e. http://msdn2.microsoft.com/en-us/library/ms141053.aspx can help you with dts* principals), but there is no centralized documentation.

My recommendation right now would be to use msdn to search for any out of the box principal you want to know more about, and typically you will find the documentation for all related principals as well.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with this
What put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:

>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this
|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:

> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with thisWhat put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:
> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
> >my msdb has been put into single user mode and i cant get it back to
> >multi_user can anyone help me with this
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with thisWhat put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:

>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:

> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

msdb is corrupt

I have a user that corrupted msdb by trying to rebuild the indexes on the
backupfile table. I have removed the suspect flag so that the database is
available. And I have the scripts to completely rebuild msdb.
In the hopes of this problem being an allocation problem, I've tried
DBCC CHECKDB
DBCC CHECKALLOC
DBCC CHECKTABLE
DBCC REINDEX
all with the same result:
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801, file
= E:\MSSQL7\DATA\msdbdata.mdf.
I also tried using bcp to get at the sysjobs table.
Besides restoring the database from a backup, which they don't have, is
there another way to get at the jobs and dts packages?I'd say its stuffed - maybe next time they'll back it up!
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> I have a user that corrupted msdb by trying to rebuild the indexes on the
> backupfile table. I have removed the suspect flag so that the database is
> available. And I have the scripts to completely rebuild msdb.
> In the hopes of this problem being an allocation problem, I've tried
> DBCC CHECKDB
> DBCC CHECKALLOC
> DBCC CHECKTABLE
> DBCC REINDEX
> all with the same result:
> Server: Msg 823, Level 24, State 1, Line 1
> I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
> page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801,
file
> = E:\MSSQL7\DATA\msdbdata.mdf.
> I also tried using bcp to get at the sysjobs table.
> Besides restoring the database from a backup, which they don't have, is
> there another way to get at the jobs and dts packages?
>|||I tend to aggree. But before I give up, I was just trying to see if there wa
s
a solution. I remember in 6.5, the contents of pages could be changed with a
dbcc command. I understand that there's huge differences between 6.5 and 7.0
.
But since the database is basicly toast, I thought it would be worth a try.
"Mary Bray" wrote:

> I'd say its stuffed - maybe next time they'll back it up!
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> file
>
>

msdb is corrupt

I have a user that corrupted msdb by trying to rebuild the indexes on the
backupfile table. I have removed the suspect flag so that the database is
available. And I have the scripts to completely rebuild msdb.
In the hopes of this problem being an allocation problem, I've tried
DBCC CHECKDB
DBCC CHECKALLOC
DBCC CHECKTABLE
DBCC REINDEX
all with the same result:
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801, file
= E:\MSSQL7\DATA\msdbdata.mdf.
I also tried using bcp to get at the sysjobs table.
Besides restoring the database from a backup, which they don't have, is
there another way to get at the jobs and dts packages?
I'd say its stuffed - maybe next time they'll back it up!
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> I have a user that corrupted msdb by trying to rebuild the indexes on the
> backupfile table. I have removed the suspect flag so that the database is
> available. And I have the scripts to completely rebuild msdb.
> In the hopes of this problem being an allocation problem, I've tried
> DBCC CHECKDB
> DBCC CHECKALLOC
> DBCC CHECKTABLE
> DBCC REINDEX
> all with the same result:
> Server: Msg 823, Level 24, State 1, Line 1
> I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
> page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801,
file
> = E:\MSSQL7\DATA\msdbdata.mdf.
> I also tried using bcp to get at the sysjobs table.
> Besides restoring the database from a backup, which they don't have, is
> there another way to get at the jobs and dts packages?
>
|||I tend to aggree. But before I give up, I was just trying to see if there was
a solution. I remember in 6.5, the contents of pages could be changed with a
dbcc command. I understand that there's huge differences between 6.5 and 7.0.
But since the database is basicly toast, I thought it would be worth a try.
"Mary Bray" wrote:

> I'd say its stuffed - maybe next time they'll back it up!
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> file
>
>

MSDB Database SUSPECT - RESET PROCEDURE ?

I have a regional server running SQL2000 and Merge replication, the MSDB, NORTHWIND and 2 User DB's have been marked suspect. I cannot get into query analyzer etc to run the necessary scripts as it is all suspect.

Any ideas on how I can reset the status on the MSDB database??
Unfortunately there is no backup of either of the 2 system databases :(

** ANY ASSISTANCE WOULD BE GREATLY APPRECIATED **

JReset database with "suspect" status:

Do the following steps from the Query Analyzer:

1) Use Master
GO
EXEC sp_resetstatus <dbname>
2) Stop & Start the SQL Server
3) Run DBCC CHECKDB
4) Do the Full database backup (Master DB included)

If the above steps didn't resolve the problem, run the query below:

Use Master
GO
EXEC sp_configure 'allow updates', 1
GO
Reconfigure with override
GO

Do the step 1 ~ 4 above, then:

Use Master
GO
EXEC sp_configure 'allow updates', 0 -- to turn off
GO
Reconfigure with override
GO

NOTE: If the database become "suspect" because it ran out of disk space, you have to do the step #1 above, then use the "ALTER DATABASE ..." to add more data file (or log file), Stop & start the SQL Server and finish with step #3 & #4 as above.

Good luck.|||Valid reponse by SVT and make sure to have regular backups for SYSTEM Databases too in future.

MSDB access removed when approle (in a user db) is set?

Our application connects to it's database using an account with minimal
rights (denydatareader and denydatawriter) and then sets an application role
with a password known only to the application.
One of the things our application is required to do is to provide details of
recent backups that have been taken of it's database, and it does this by
performing SELECTs on tables in the MSDB database. I believe it can do that
because of the existence of the guest account provided by default in that
database?
However when we install our app onto a server that has this "guest" account
removed from MSDB, our application's login can no longer access the MSDB
tables. And when we explicitly add the specific account as a user in the
MSDB database it can once again from QA but not from within the application.
I think this is because setting the approle inside the application is
somehow affecting the identity of the user in the MSDB database?
Does this sound reasonable? Do you have any ideas of what else I could look
at or for?
regards,
Paul Ritchie.> I think this is because setting the approle inside the application is
> somehow affecting the identity of the user in the MSDB database?
Yes, once an app role is activated, other databases can be accessed only as
guest.
If you don't want to rely on the guest account in msdb, you can add your
application account to msdb (default public role) and execute those queries
without the application role activated on that connection. Other queries
can be run with the application role activated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:u0BU6FnAEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Our application connects to it's database using an account with minimal
> rights (denydatareader and denydatawriter) and then sets an application
role
> with a password known only to the application.
> One of the things our application is required to do is to provide details
of
> recent backups that have been taken of it's database, and it does this by
> performing SELECTs on tables in the MSDB database. I believe it can do
that
> because of the existence of the guest account provided by default in that
> database?
> However when we install our app onto a server that has this "guest"
account
> removed from MSDB, our application's login can no longer access the MSDB
> tables. And when we explicitly add the specific account as a user in the
> MSDB database it can once again from QA but not from within the
application.
> I think this is because setting the approle inside the application is
> somehow affecting the identity of the user in the MSDB database?
> Does this sound reasonable? Do you have any ideas of what else I could
look
> at or for?
> regards,
> Paul Ritchie.
>
>|||Dan - you're a champion. Much appreciated!
cheers,
Paul.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OCQXoarAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> Yes, once an app role is activated, other databases can be accessed only
as
> guest.
> If you don't want to rely on the guest account in msdb, you can add your
> application account to msdb (default public role) and execute those
queries
> without the application role activated on that connection. Other queries
> can be run with the application role activated.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:u0BU6FnAEHA.1548@.TK2MSFTNGP12.phx.gbl...
> role
details
> of
by
> that
that
> account
the
> application.
> look
>