SQL Server 2000 object ownership

[GL3N]

I want to use Enterprise Manager to script all stored procedures on a SQL7 database, then run that script on an SQL2000 database to bring it in line.

The script built out of the SQL7 box contains statements to DROP [DBO].[<procedure name>], but the statements which CREATE the new procedures do not have the [DBO] ownership on them.

This script runs fine on other SQL7 databases, which seem not to mind the missing [dbo] on the CREATE statements.

SQL2000 seems to create the procedures without an ownership context, resulting in there being no [dbo] procedure to DROP if the script should ever be run again in the future.

How is this resolved?

 

[vadimg]

GLN, If you are running this script on another server, you are free to choose who the owner should be.

If you run it under 'sa' user account, the owner will still be DBO.

If you run it under another user account 'user1' (who has permission to create sps), 'user1' will become the owner. And the script you take off of that database, will now read: DROP [user1].[<procedure name].

Hope this helps.

 

[tlbroadbent]

NOTE: If user1 is assigned to the System Administrator role in SQL 7 or 2000, user1 will create objects as dbo not user1. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.