problem with SQL Agent

OK this is a new error on me.
I have a SQL server 2000 on Windows Server 2003.
I have setup a Maint plan that backs up the user and system databases
and the transaction Logs on my user databases.
Does optimises and integrity checks on those databases as well.
I am not attempting to repair any minor problems.
I am not performing integrity checks before backing up the database
and transaction logs.
All of the databases that I am doing a transaction log backup on have
recovery mode of full.
When the jobs run they fail instantly with error code 22029.
Sqlmaint.exe failed to run. This was working up until the end of the
day on the Thursday before Good Friday. (last week) The transaction
log backup ran fine at 4:00 PM the same job failed at 8:00 PM. No
changes were made to the server during that time.
Another issue that started at the same time. I have a web page used to
grab data from a text file export and pull it into an AS400. I use
this server to house the DTS packages and stored procedures that the
process needs. I suddenly quit working when the jobs quit running.
We are in a mixed enviroment with most clients on AD and this server
and most others still in a NT 4 domain. We have been like this for at
least 3 months and everything has worked fine. I setup the various
users and systems the way the need to be to work.
Originally the SQL Agent was running as a generic NT 4 Domain user with
local administrator rights to the local box and server role of system
administrator. The MSSQLSERVER and DTC services were running as the
built in system account.
Everything was happy then it broke.
When I reboot the server the jobs and the website work fine. Then
after a period of time that I have not figured out yet, but more than
15 minutes and less than 2 hours. Everything quits working.
I have changed all the services to run as a domain admin with SQL
system adminsitrator server role. Still same thing.
I have changed all the services to run as the built in system account.
Still same thing.
I run the sqlmaint from command line with the switches that I am using
in the maint jobs and it runs fine.
I have run dbcc checkdb on all the databases and they are fine.
The DTS packages and the stored procedures used by the website run fine
from with in Query analyzer.
Any help you can provide would be greatly appreciated.A guess: Try disabling the IPSec service. I've seen some similar behavior
when this service was misconfigured.
Also I would make sure the system is configure to log all security failures
and take a good look at the Security event log for any additional clues.|||Kevin,
Thanks for the reply. I already had the security set to log failure
and it is showing nothing but success audits.
I stopped the IPSec service and I still couldn't run the job. Do i
need to restart the server after making that change?
Kevin Joyner
Kevin English wrote:
> A guess: Try disabling the IPSec service. I've seen some similar behavior
> when this service was misconfigured.
> Also I would make sure the system is configure to log all security failures
> and take a good look at the Security event log for any additional clues.

Problem with sp_OACreate

The sp_OACreate fails at nights it works fine at daytime, I checked the
Event Log, and saw below error message for every databases I have.
18278 :
Database log truncated: Database: DBNAME.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:523AA016-9F7B-4CDA-A3DA-809E79084527@.microsoft.com...
> Hi
> You may want to try turning it off for a period and see if the problem
> persists. You need to asses the risk of doing this, such as
> http://support.microsoft.com/defaul...kb;en-us;309422
> John
> "Erdal Akbulut" wrote:
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp[/ur
l]
any
ActiveX
Windows
ActiveXHi
I don't think that has anything to do with the error! You may want to check
if there are any scheduled virus scans at the time your error occurs.
John
"Erdal Akbulut" wrote:

> The sp_OACreate fails at nights it works fine at daytime, I checked the
> Event Log, and saw below error message for every databases I have.
> 18278 :
> Database log truncated: Database: DBNAME.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:523AA016-9F7B-4CDA-A3DA-809E79084527@.microsoft.com...
> http://msdn.microsoft.com/library/d...
a-oz_3enj.asp
> any
> ActiveX
> Windows
> ActiveX
>
>|||Hi,
We have a wly scheduled Virus Scan on that machine.sp_OACreate fails more
than once in a w
Thanks,
Erdal
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:24CDDF90-D6DF-4BA7-A02D-84B6F5A5308A@.microsoft.com...
> Hi
> I don't think that has anything to do with the error! You may want to
check
> if there are any scheduled virus scans at the time your error occurs.
> John
> "Erdal Akbulut" wrote:
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp[/ur
l]
there
using|||Hi
You may be better migrating to CDO.
John
"Erdal Akbulut" wrote:

> Hi,
> We have a wly scheduled Virus Scan on that machine.sp_OACreate fails mo
re
> than once in a w
> Thanks,
> Erdal
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:24CDDF90-D6DF-4BA7-A02D-84B6F5A5308A@.microsoft.com...
> check
> http://msdn.microsoft.com/library/d...
a-oz_3enj.asp
> there
> using
>
>|||John,
I am not sure if I can use CDOSYS.dll instead of CDONOTS.dll in my VB6
application. Actualy The Server is Win2003 and there was no CDONTS.dll I
copied and registered that dll.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0FBF1B85-A949-45A5-B2FE-B9FB780A4C25@.microsoft.com...
> Hi
> You may be better migrating to CDO.
> John
> "Erdal Akbulut" wrote:
>
more
the
problem
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp[/ur
l]
VB
moved to
create|||Hi
I am still not sure why it runs during the day and not at night using the
same process, therefore to eliminate any incorrect configuration of
cdonts.dll it would be safer to use the native cdo. If that fails then the
issue is probably external to CDO.
John
"Erdal Akbulut" wrote:

> John,
> I am not sure if I can use CDOSYS.dll instead of CDONOTS.dll in my VB6
> application. Actualy The Server is Win2003 and there was no CDONTS.dll I
> copied and registered that dll.
> Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0FBF1B85-A949-45A5-B2FE-B9FB780A4C25@.microsoft.com...
> more
> the
> problem
> http://msdn.microsoft.com/library/d...
a-oz_3enj.asp
> VB
> moved to
> create
>
>

Problem with size of databse sql server 7

2 databases (sql server 7) have been created in same conditions : same tools ("bcp...") and same options
On both 2 series of "users tests" have been executed. These tests were exactly the same
At the end we have executed a "backup" procedure on each database
We expected the same size for the two backup database but we obtained two different results :
304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction
Could you help us explaining this difference of almost 2 Mo ?No need to repost after only 25 minutes. See my other reply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Laurent L. (France)" <llechat@.sodifrance.fr> wrote in message
news:4987F961-0A67-438B-94A0-439687537630@.microsoft.com...
> 2 databases (sql server 7) have been created in same conditions : same tools ("bcp...") and same
options.
> On both 2 series of "users tests" have been executed. These tests were exactly the same !
> At the end we have executed a "backup" procedure on each database.
> We expected the same size for the two backup database but we obtained two different results :
> 304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction.
> Could you help us explaining this difference of almost 2 Mo ?

Problem with size of database sql server 7

2 databases (sql server 7) have been created in same conditions : same tools "bcp..." and same options.
On both 2 series of "users tests" have been executed. These tests were exactly the same !
At the end we have executed a "backup" procedure on each database.
We expected the same size for the two backup database but we obtained two different results :
304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction.
Could you help us explaining this difference of almost 2 Mo ?I wouldn't worry about that small size difference in backup. Are the tsql SQL Server of the same
service pack? Also, do you do *exactly* the same for the two, all the way since the database
creation. If you want to pursuit this (I wouldn't), then I recommend that you have a scrip file
which includes the CREATE DATABASE command to exclude as much "noise" as possible,
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Laurent L. (France)" <llechat@.sodifrance.fr> wrote in message
news:649E6E72-A43F-4084-88BB-48ACDF8AECC1@.microsoft.com...
> 2 databases (sql server 7) have been created in same conditions : same tools "bcp..." and same
options.
> On both 2 series of "users tests" have been executed. These tests were exactly the same !
> At the end we have executed a "backup" procedure on each database.
> We expected the same size for the two backup database but we obtained two different results :
> 304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction.
> Could you help us explaining this difference of almost 2 Mo ?

Problem with sample databases

I have installed SQL Express 2005 together with VS 2005. After a while I found out that the sample databases (Northwind and Pubs) were missing, so I installed them. According to the 'Readme', which comes with the installation I could choose between attaching the db to the Server or running a SQL script.

Neither works. I've read this link about remote connections (http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx). My development machine is just a local single computer and I still get this error message:

'An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default settings SQL Server does not allow remote connections.'

I' ve opened TCP/IP and Named Pipes and I get the same result when trying to connect from the Server Explorer in VS.

Thanks for your help.

Hey,

SQL Server 2005 doesn't install with the sample databases anymore; I am having training on it, and that is what I just found out. Also, to setup remote connections, you can do so in the SQL Server Surface Area Configuration.

Brian

|||

Hey,

TCP/IP and Named Pipes are only selectable if you allow remote connections in the SQL Server Surface Area Configuration.

RTFT.

Anybody else coming up with an idea?

Problem with role permissions

We are experiencing a problems setting permissions on roles on SQL Server
2000. Our SQL server is holding several databases (60+) and on some of them
(not all), when we change permissions on tables for a role (Public for
example), they do not seam to be saved.We tried through Enterprise Manager
and SQL Query Analyser with the same result. We change the permission,
click on OK, go out of EM and when we go back in, the permissions are back
the way they were before we changed them What is puzzling us is that it
works fine on some databases and not on other. We do not know of any
database options that would prevent changing the permissions, is there one?
Anyone as any taught about this?
MS SQL Server 2000 (sp3a)
Windows 2000 (sp4)
C.R.See Books onLine:
Resolving Permission Conflicts
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80
The public role is a special database role to which every database user
belongs. The public role:
Captures all default permissions for users in a database.
Cannot have users, groups, or roles assigned to it because they belong to
the role by default.
Is contained in every database, including master, msdb, tempdb, model, and
all user databases.
Cannot be dropped
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
You mis-understood my email. I mentioned the Public role as an EXAMPLE. We
are not trying to drop the public role, or any other role. We are trying to
set the premissions for the role(s). It is happening for ANY role we
created and the public role. We are trying to revoke all rights to the role
public and grant access to some other role(s). The changed permissions are
not saved by the server, weither we use EM or Query Analyzer to do this.
C.R.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:WrNRXxCwDHA.1868@.cpmsftngxa07.phx.gbl...

quote:

---

> See Books onLine:
> Resolving Permission Conflicts
> mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80
> The public role is a special database role to which every database user
> belongs. The public role:
> Captures all default permissions for users in a database.
> Cannot have users, groups, or roles assigned to it because they belong to
> the role by default.
> Is contained in every database, including master, msdb, tempdb, model, and
> all user databases.
> Cannot be dropped
> To protect against unauthorized data access, minimize the permissions
> granted to the public role. Instead, grant permissions to other database
> roles and to user accounts associated with logins.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||from previous thread
" We are trying to set the premissions for the role(s). It is happening
for ANY role we
created and the public role. We are trying to revoke all rights to the
role public and grant access to some other role(s). The changed
permissions are not saved by the server, weither we use EM or Query
Analyzer to do this."
I would run profiler to capture this. Create a sample database, list out
the tsql commands and trace it using Profiler.
As Richard mentioned, if you have a repro of this, open up a case with us
and provide us the details.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We encounter the same problem like C.R. (with SQL200 sp3)
"We change the permission, click on OK, go out of EM and when we go back =
in, the
permissions are back the way they were before we changed them"
Danny
"C.R." a =E9crit :

quote:

---

> We are experiencing a problems setting permissions on roles on SQL Serv=

---

er

quote:

---

> 2000. Our SQL server is holding several databases (60+) and on some of=

---

them

quote:

---

> (not all), when we change permissions on tables for a role (Public for
> example), they do not seam to be saved.We tried through Enterprise Mana=

---

ger

quote:

---

> and SQL Query Analyser with the same result. We change the permission,=

---

quote:

---

> click on OK, go out of EM and when we go back in, the permissions are b=

---

ack

quote:

---

> the way they were before we changed them What is puzzling us is that i=

---

t

quote:

---

> works fine on some databases and not on other. We do not know of any
> database options that would prevent changing the permissions, is there =

---

one?

quote:

---

> Anyone as any taught about this?
> MS SQL Server 2000 (sp3a)
> Windows 2000 (sp4)
> --
> C.R.

---

|||I made a mistake
with SQL200 sp3a
Danny
Danny Presse a =E9crit :

quote:

---

> We encounter the same problem like C.R. (with SQL200 sp3)
> "We change the permission, click on OK, go out of EM and when we go bac=

---

k in, the[QUOTE]
> permissions are back the way they were before we changed them"
> Danny
> "C.R." a =E9crit :
>
rver[QUOTE]
of them[QUOTE]
r[QUOTE]
nager[QUOTE]
n,[QUOTE]
back[QUOTE]
it[QUOTE]
[QUOTE]
e one?[QUOTE]

problem with orphaned "dbo" user of an attached database

Hi everybody,
I'm in a situation where I allow my users of SQL Server the creation of her
own databases. This is been done in order to allow the "creator" (the owner)
full acces to the created DB. Everything works fine, as long as the DB is
created from scratch, like:
CREATE DATABASE universe
But additionally, my users are also exchanging databases, so they will also
use
CREATE DATABASE universe ... FOR ATTACH
When attaching a database, the owner of the so created database is the login
that
performed the operation, which is fine. But, the "dbo" user in the attached
database is still associated with the original login (before detaching the
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know that
this can be fixed with
ALTER AUTHORIZATION ON DATABASE::universe to [login]
So, after attaching a database, the user can simply all ALTER AUTHORIZATION.
My problem is that ALTER AUTORIZATION requires "CONTROL SERVER" permission,
which is simply a synonym for "sysadmin" role membership and therefore not
what I want. All I want my users grant is "CREATE ANY DATABASE" permission.
Does anybody know a solution besides doing the CREATE DATABASE ... FOR
ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
regarding signature?
Thanks,
HolgerHolger (Holger@.discussions.microsoft.com) writes:
> I know that this can be fixed with ALTER AUTHORIZATION ON
> DATABASE::universe to [login] So, after attaching a database, the user
> can simply all ALTER > AUTHORIZATION. My problem is that ALTER
> AUTORIZATION requires "CONTROL SERVER" permission, which is simply a
> synonym for "sysadmin" role membership and therefore not what I want.
> All I want my users grant is "CREATE ANY DATABASE" permission. Does
> anybody know a solution besides doing the CREATE DATABASE ... FOR ATTACH
> with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
> regarding signature?
You could put the ALTER AUTHORSIZATION statement in a stored procedure
that you sign with a certificate, and then you grant a login associated
with that cert CONTOL SERVER. Note that the login is not a real login,
that is, it cannot connect.
For a lot more detail on this, see this article on my web site:
http://www.sommarskog.se/grantperm.html
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|||Holger
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
What is authentication are you using?
See if this helps
http://dimantdatabasesolutions.blog...on.
html
"Holger" <Holger@.discussions.microsoft.com> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
> Hi everybody,
> I'm in a situation where I allow my users of SQL Server the creation of
> her
> own databases. This is been done in order to allow the "creator" (the
> owner)
> full acces to the created DB. Everything works fine, as long as the DB is
> created from scratch, like:
> CREATE DATABASE universe
> But additionally, my users are also exchanging databases, so they will
> also
> use
> CREATE DATABASE universe ... FOR ATTACH
> When attaching a database, the owner of the so created database is the
> login
> that
> performed the operation, which is fine. But, the "dbo" user in the
> attached
> database is still associated with the original login (before detaching the
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
> this can be fixed with
> ALTER AUTHORIZATION ON DATABASE::universe to [login]
> So, after attaching a database, the user can simply all ALTER
> AUTHORIZATION.
> My problem is that ALTER AUTORIZATION requires "CONTROL SERVER"
> permission,
> which is simply a synonym for "sysadmin" role membership and therefore not
> what I want. All I want my users grant is "CREATE ANY DATABASE"
> permission.
> Does anybody know a solution besides doing the CREATE DATABASE ... FOR
> ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with
> a
> regarding signature?
> Thanks,
> Holger|||Hi Erland,
thanks for your answer. I know that this is a solution but the problem is
that, for ALTER AUTHORIZATION I need a login and a certificate inside the
master database and also a master key.
I'd like to fairly avoid storing information of any kind inside master -
regardless of what information this is. That's the reason why I was asking
for a solution without a signed stored procdure.
"Erland Sommarskog" wrote:

> Holger (Holger@.discussions.microsoft.com) writes:
> You could put the ALTER AUTHORSIZATION statement in a stored procedure
> that you sign with a certificate, and then you grant a login associated
> with that cert CONTOL SERVER. Note that the login is not a real login,
> that is, it cannot connect.
> For a lot more detail on this, see this article on my web site:
> http://www.sommarskog.se/grantperm.html
> --
> 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
>|||Hi Uri,
thanks for your answer. As for our SQL Server - we use Windows
authentication. But I do know nothing about the source Server,so it should
work with all authentication modes. Also I guess, in order work with Windows
authentication, the prerequisite will certainly be that all servers reside i
n
one domain, which is not the case. I get databases from unkown sources from
all over the world and simply have to use those.
regards,
Holger
"Uri Dimant" wrote:

> Holger
> What is authentication are you using?
> See if this helps
> http://dimantdatabasesolutions.blog...o
n.html
>
>
> "Holger" <Holger@.discussions.microsoft.com> wrote in message
> news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
>
>|||Holger (Holger@.discussions.microsoft.com) writes:
> thanks for your answer. I know that this is a solution but the problem is
> that, for ALTER AUTHORIZATION I need a login and a certificate inside the
> master database and also a master key.
> I'd like to fairly avoid storing information of any kind inside master -
> regardless of what information this is. That's the reason why I was asking
> for a solution without a signed stored procdure.
If you prefer you can use impersonation in the procedure, that is EXECUTE
AS.
I'm not sure that I understand your reluctance against storing information
in master. After all, it is here you have information about logins,
databases, server-level permissions, the service master key in master.
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