SQL 2000: scripting and ownership
From: Bill Morris (seamlyne_at_hotmail.com)
Date: 07/06/04
- Next message: Vols Fan: "Re: delete failing after backup job"
- Previous message: Mike: "Windows 2003 Hosting, SQL Included"
- Messages sorted by: [ date ] [ thread ]
Date: 6 Jul 2004 10:36:51 -0700
Windows 2000/2003 Server, MS SQL Server 2000
I've got an issue with scripting and object ownership. There are
three logins/users of our database, and the main ASP application uses,
for example, "USER1". For a variety of reasons, I've gotten out of
the habit of using DTS:Copy Objects and Data (that misses defaults and
primary keys, for starters) and DTS:Copy table(s)... (that assigns any
object transferred to whatever you login with - WRONG!) to move things
around. My method, while a little lengthy, works very well: script
all objects and run the script in the appropriate database in Query
Analyzer. Then login source and destination in each of the three
users/logins and transfer only those objects owned by the logins.
Simple, effective, almost fool proof.
Now, however, I'm getting some odd behavior. Certain objects are
being scripted without the owner name, particularly in views and
stored procedures. Where the view on SVR_SOURCE would say "select
firstname, lastname from owner2.People" it's being scripted as "select
firstname, lastname from People", and the view creation fails because
there is no "{implied dbo.}People".
In this database there are ~60 views, ~500 stored procedures, ~400
tables. Fixing the scripts by hand would not be a pleasant task. Any
ideas what's going on? Something in Windows 2003 where the scripting
is being done? Something in the SQL Server settings on the 2003
server?
- Wm
William Morris, Seritas LLC
Liberty, Missouri USA
- Next message: Vols Fan: "Re: delete failing after backup job"
- Previous message: Mike: "Windows 2003 Hosting, SQL Included"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
