Enterprise Mgr - alter table fails

My developers just started having this problem a few weeks ago. Nearly
any type of "design" table changes are failing in Enterprise Mgr. This
only seems to be happening on the servers running Enterprise Edition.
Standard Edition and MSDE do not have the problem.
All are running SQL2000 - SP4. Some servers are Win2000 Server SP4 and
others are Win2003 Server - SP4. The Windows version does not seem to
matter. I am not aware of any maintenace applied to the SQL servers,
but all workstations have recently had automatic updates turned on and
have received some updates.
Here is the most common example. The developer needs to change the
length of a field in a table. They use Enterprise Mgr, right click on
the table and select "design". They make their change and when they
hit save, they get the following error. (There is no data in the
table.)
'prob1' table
- Unable to create index 'PK_prob1'.
ODBC error: MicrosoftODBC SQL Server DriverSQL ServerTable 'prob1'
already has a primary key defined on it.
MicrosoftODBC SQL Server DriverSQL ServerCould not create constraint.
See previous errors.
I can not just tell them to switch over and use Query Analyzer. Some
of these people have little or no SQL knowledge and would never make it
through alter table statements. They build tables for their front page
apps and that's as far as they get into SQL.
Any thoughts would be helpful. I've scoured support and newsgroups and
can only find old 6.5 and 7.0 reports. I've included the DDL to create
the table if needed. I get the error just changing F2 from 20 to 25
for example.
if exists (select * from dbo.sysobjects where id =
object_id(N'dbo.prob1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.prob1
GO
CREATE TABLE dbo.prob1 (
f1 char (10) NOT NULL ,
f2 char (20) NULL ,
f3 char (30) NULL
) ON PRIMARY
GO
ALTER TABLE dbo.prob1 ADD
CONSTRAINT PK_prob1 PRIMARY KEY CLUSTERED
(
f1
) ON PRIMARY
GO
Hi,
Just a small thought..
As you have applied SP4 to your SQL Servers, have you applied SP4 to all of
the development machines where the client tools (EM, QA) are installed, too?
Robert
<stephanie.harrell@.stateauto.com> wrote in message
news:1142271519.219796.202000@.i40g2000cwc.googlegr oups.com...
> My developers just started having this problem a few weeks ago. Nearly
> any type of "design" table changes are failing in Enterprise Mgr. This
> only seems to be happening on the servers running Enterprise Edition.
> Standard Edition and MSDE do not have the problem.
> All are running SQL2000 - SP4. Some servers are Win2000 Server SP4 and
> others are Win2003 Server - SP4. The Windows version does not seem to
> matter. I am not aware of any maintenace applied to the SQL servers,
> but all workstations have recently had automatic updates turned on and
> have received some updates.
> Here is the most common example. The developer needs to change the
> length of a field in a table. They use Enterprise Mgr, right click on
> the table and select "design". They make their change and when they
> hit save, they get the following error. (There is no data in the
> table.)
> 'prob1' table
> - Unable to create index 'PK_prob1'.
> ODBC error: MicrosoftODBC SQL Server DriverSQL ServerTable 'prob1'
> already has a primary key defined on it.
> MicrosoftODBC SQL Server DriverSQL ServerCould not create constraint.
> See previous errors.
>
> I can not just tell them to switch over and use Query Analyzer. Some
> of these people have little or no SQL knowledge and would never make it
> through alter table statements. They build tables for their front page
> apps and that's as far as they get into SQL.
> Any thoughts would be helpful. I've scoured support and newsgroups and
> can only find old 6.5 and 7.0 reports. I've included the DDL to create
> the table if needed. I get the error just changing F2 from 20 to 25
> for example.
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'dbo.prob1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table dbo.prob1
> GO
> CREATE TABLE dbo.prob1 (
> f1 char (10) NOT NULL ,
> f2 char (20) NULL ,
> f3 char (30) NULL
> ) ON PRIMARY
> GO
> ALTER TABLE dbo.prob1 ADD
> CONSTRAINT PK_prob1 PRIMARY KEY CLUSTERED
> (
> f1
> ) ON PRIMARY
> GO
>
|||Yes, all servers have SP4. As for client tools, I would say the
majority are. There may be a couple older machines that are not
current, but have not heard from any of those people - if there are any.
|||(stephanie.harrell@.stateauto.com) writes:
> Here is the most common example. The developer needs to change the
> length of a field in a table. They use Enterprise Mgr, right click on
> the table and select "design". They make their change and when they
> hit save, they get the following error. (There is no data in the
> table.)
> 'prob1' table
> - Unable to create index 'PK_prob1'.
> ODBC error: MicrosoftODBC SQL Server DriverSQL ServerTable 'prob1'
> already has a primary key defined on it.
> MicrosoftODBC SQL Server DriverSQL ServerCould not create constraint.
> See previous errors.
>
> I can not just tell them to switch over and use Query Analyzer. Some
> of these people have little or no SQL knowledge and would never make it
> through alter table statements. They build tables for their front page
> apps and that's as far as they get into SQL.
To be very blunt, if they can't write change scripts with ALTER TABLE,
they should not be changing tables at all. Performing changes to tables
is an advanced operation, and requires careful understanding, particularly
if you are to do it in a production environment.
The Design Table function in EM has several serious flaws (and all these
flaws are carried over to SQL 2005). I include a list below, from a post
that I made just the other day.
I was not able to reproduce the problem with the table you posted. But
you could get an idea of what's going by requesting getting a change
script from EM. (Which you should do anyway, because of all the bugs
in the Table Designer.) You find this function on the 3rd button from
the left in the toolbar.
Here is a list of know defenciencies with the Table Designer:
1) The transaction scope is wacko. What should be one transaction is
split up into several, which can lead to a table change being only
partly implemented, and your database becomes a mess. Or you just lose
constraints, without knowing it.
2) In the generated script, the transaction spans multiple batches. This
means that if one batch fails with a batch-aborting error, the
transaction is rolled back. The remaining batches in the script are
still carried out which is not likely what you want. (This flaw does
not appear if you save directly, as the Table Designer does not continue
to submit batches if there is an error.)
3) The table designer essentially behaves as there has been no later
version of SQL Server. That is, in many cases where it could use
ALTER TABLE, it instead runs a script where it creates a new table
and copies data over.
4) Say that you have to tables A and B. B has an FK to A. You first make a
change to B, and generate a script. Then you change your mind, and
close without saving anything. Instead you make a change A. When you
generate script (or even worse just press Save), you find that the
change to B is included.
5) All constraints that are reapplied, are reapplied WITH NOCHECK,
which means that they are not trusted as far as the optimizer is
concerned. This have serious impact on performance, not the least
with partitioned views.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thank you for your reply and you are certainly entitled to your opinion
on who should change tables. Our set up works for us. We support a
large audience of developers at varying levels of expertise and this is
the first time we've encountered any problems with the tool. I will
pass along your suggestion on generating the change script to our
developers.
|||(stephanie.harrell@.stateauto.com) writes:
> Thank you for your reply and you are certainly entitled to your opinion
> on who should change tables. Our set up works for us. We support a
> large audience of developers at varying levels of expertise and this is
> the first time we've encountered any problems with the tool.
That you know of. Some of the problems that I pointed out are of the
kind that you may not notice directly when they cause problems. If you
lose an FK constraint, you may not notice until years later when you find
some junk in the column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||I thought I would let you know that I found the problem. The
XACT_ABORT option had been turned on server-wide. The new tools with
SQL 2005 allow you to turn that option off/on in the server properties
and someone had turned it on. Once I turned it back off, Enterprise
Manager worked like a champ again. Guess this is why we have
development servers - to learn lessons the hard way.
Thanks all!
Erland Sommarskog wrote:
> (stephanie.harrell@.stateauto.com) writes:
> That you know of. Some of the problems that I pointed out are of the
> kind that you may not notice directly when they cause problems. If you
> lose an FK constraint, you may not notice until years later when you find
> some junk in the column.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Enterprise manager security?

Our product has the middleware conduct transactions with the SQL server usin
g a special account. The developers know the account and password in order t
o code it in their apps. The problem is that they are not allowed access to
the platform once an app g
oes into production (we are in the banking industry). However, they use Ente
rprise Manager and as long as they know the account name and password, they
can still get to any of the databases.
Anyone know how to stop a connection to a SQL DB (by user) with Enterprise M
anager?It is bad practice to code the account and password in application. This
should be stored in an ini file or the registry (and encrypted of course) so
it can be changed easily. In that case you can set up a new account with the
same permissions and delete the old account, and make your application use
it by changing the username and password in the registry or ini file. If you
would like to store the username and paswword encoded in the application for
some reason, at least store it in a separate class, so that you can build a
secure release version by only including a different file for that class.
Blocking them from Enterprise Manager doesn't help much, how long do you
think it takes a not even very talented developer to knock up a basic app
that allows you to view tables in any database? Or they can use Query
Analyzer or osql.
Which bank uses this btw, I hope it's not mine ;-)
Jacco Schalkwijk
SQL Server MVP
"rick m" <anonymous@.discussions.microsoft.com> wrote in message
news:03A80E82-BAF0-4055-AEA7-9C33AEB30DB8@.microsoft.com...
> Our product has the middleware conduct transactions with the SQL server
using a special account. The developers know the account and password in
order to code it in their apps. The problem is that they are not allowed
access to the platform once an app goes into production (we are in the
banking industry). However, they use Enterprise Manager and as long as they
know the account name and password, they can still get to any of the
databases.
> Anyone know how to stop a connection to a SQL DB (by user) with Enterprise
Manager?|||Rick,
You have stumbled onto one of the most overlooked internal security problem
with SQL Server. Appliction developers often know logons and passwords and a
re able to connect to production databases outside of the application using
Enterprise Manager, Query A
nalyzer, Access, a custom app. etc. If you cannot get the app developers to
use Windows authentication, you really need to store the login and password
in a safe ini file or registry setting and make sure the password changes as
the application goes into
production.
Your not the only one with the same question, many other have written the sa
me question and apparently the problem with application developers being abl
e to review production data is starting to become a problem.
Sorry, there is not a good answer to your question, it is a application deve
lopment process change and not just a setting that will eventually keep appl
ication developers out of your production database.
About the only thing you can do in the short-term is have management send ou
t a warning that no app developer should be in production and then audit ent
ry into your production db and have the offending developers "taken" care of
.
Randy Dyess
www.Database-Security.Info|||"rick m" <anonymous@.discussions.microsoft.com> wrote in message
news:03A80E82-BAF0-4055-AEA7-9C33AEB30DB8@.microsoft.com...
> Our product has the middleware conduct transactions with the SQL server
using a special account. The developers know the account and password in
order to code it in their apps. The problem is that they are not allowed
access to the platform once an app goes into production (we are in the
banking industry). However, they use Enterprise Manager and as long as they
know the account name and password, they can still get to any of the
databases.
> Anyone know how to stop a connection to a SQL DB (by user) with Enterprise
Manager?
No direct way, you'd be better off blocking (or simply removing any security
permissions) for user connections to the server running SQL Server. I agree
with the other posts, the main thing to emphasize -- use Windows
Authentication! That eliminates the need to hard code user names/passwords.
Security can be implemented and secured based on NT groups.
Steve