February 7, 2007 at 11:36 am
Objects like views, functions, and procedures that include the Create as part of the object will be scripted out by EM exactly as originally written.
So if the user creates a procedure using "Create Procedure dbo.ProcedureName", that is how EM will script it out. It all goes back to the developers creating it appropriately in the first place.
February 7, 2007 at 2:15 pm
As far as scripting out of EM there is a pitfall that we have stumbeled on...
When scripting out a table object with a constraint you will notice EM scripts this constraint out WITH NOCHECK. For this example it is the Primary Key of the table. This was not how the table script looked when I created it or when I scripted it out of query analyzer.
Use this and create the tables locally
USE [tempdb]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mytable]
GO
CREATE TABLE dbo.[mytable]
(
[someId] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
,[someFKCol] int NOT NULL
,[somedesc] varchar(25) NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTableFk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTableFk]
GO
CREATE TABLE dbo.[myTableFk]
(
[someFKId] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
)
GO
ALTER TABLE dbo.[mytable] ADD
CONSTRAINT FK_mytable
FOREIGN KEY (someFKCol)
REFERENCES myTableFk (someFKId)
GO
Now use EM and script the table out (with the following options Script Indexes,Sript Primary Keys,.........and use File format = (ANSI) and preview...you get the following
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mytable]
GO
CREATE TABLE [dbo].[mytable] (
[someId] [int] IDENTITY (1, 1) NOT NULL ,
[someFKCol] [int] NOT NULL ,
[somedesc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[someId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mytable] ADD
CONSTRAINT [FK_mytable] FOREIGN KEY
(
[someFKCol]
) REFERENCES [dbo].[myTableFk] (
[someFKId]
)
GO
Notice the profound difference your primary key has NOCHECK added to the ALTER statement. Just a heads up when using EM to script out your objects.
February 7, 2007 at 2:31 pm
You must be sysadmin to create dbo tables, and then it does it automatically.
db_owner is not the same as dbo.
February 7, 2007 at 2:45 pm
Actually, you just must be aliased as dbo. Members of the sysadmin fixed server role are aliased as dbo. So is the database owner. For instance, create a SQL Server login that is not a member of the sysadmin fixed server role. Create a test database. Change the owner to that login (do not add the login as a user). Then log in to the server as that login and create an object. You'll see it has dbo as an owner. Using the sp_addalias trick from SQL Server 6.5 works too, but it's deprecated.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply