DDl Triggers for tables...

Hi,

I have a scenario in which I need to restrict schema changes for around 5 tables only in a database. When changes are done to the other tables, it should be allowed. I am planning to use DDL Trigger. But DDL Trigger is having only 2 options in the ON Clause like ON DATABASE and ON ALL SERVERS. If i use On Database I will not be able to make modifications in the other tables.

Is there any way i could achieve my requirement?

Regards,

Swapna.B.

Move the thread to "SQL Server Database Engine" forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1|||

If you define your trigger on the DDL_TABLE_EVENTS event group then, within the trigger, you can parse the EventData function's value to return the following values when an ALTER TABLE statement is issued:

<EVENT_INSTANCE>
 <EventType>type</EventType>
 <PostTime>date-time</PostTime>
 <SPID>spid</SPID>
 <ServerName>name</ServerName>
 <LoginName>name</LoginName>
 <UserName>name</UserName>
 <DatabaseName>name</DatabaseName>
 <SchemaName>name</SchemaName>
 <ObjectName>name</ObjectName>
 <ObjectType>type</ObjectType>
 <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

Inside the trigger's code you should check to see if the ObjectName and SchemaName values match those of any of the tables that you want to preserve and then issue a rollback command if necessary.

Check out the EVENTDATA Function topic in BOL for more info.

Chris

|||

Hi,

Thanks a lot for the help provided. I have achieved the requirement by using eventdata function and validating the values in a separate sp.

I have the DDL trigger which calls a stored procedure. This SP does the segregating of values from eventdata function , validating those values and commits or rollsback according to the objectname retrieved. Now its working fine. But i face one error like the one below.

When ever the DDL Trigger is executed, it throws an error like

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The transactions are getting completed successfully but this error occurs everytime we try to make changes to any table in the database.

What is the reason for this error? Kindly let me know the way to avoid it.

One more point here is when we try to execute a batch of alter statements without go command in the database in which the DDL trigger is present, Only the first one gets executed other statements are not getting considered. Is this anyway related to the error above?

Thanks and Regards,

Swapna.B.

|||

Could you post the definitions of both your trigger and your stored proc?

Chris

|||

Hi Chris,

Here is the definition of my trigger and Stored Procedure.

Trigger :

USE [Jeux]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create trigger [DDL_TRG_DB] on database for ALTER_TABLE as

set ANSI_NULLS ON

set ANSI_PADDING ON

set ANSI_WARNINGS ON

set ARITHABORT ON

set CONCAT_NULL_YIELDS_NULL ON

set NUMERIC_ROUNDABORT OFF

set QUOTED_IDENTIFIER ON

declare @.EventData xml

set @.EventData=EventData()

exec sp_Sample @.EventData, 1

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_TRG_DB] ON DATABASE

Stored Procedure :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_Sample]

(

@.EventData xml

,@.procmapid int

)

AS

begin

set nocount on

if is_member('db_owner') <> 1

begin

raiserror (21050, 16, -1)

return (1)

end

-- validate the procmapid

if @.procmapid not in (1,2,3,4)

begin

raiserror(15021, 16, -1, '@.procmapid')

Rollback Transaction

Return (1);

end

declare @.object_name sysname

,@.object_owner sysname

,@.qual_object_name nvarchar(512) --qualified 3-part-name

,@.objid int

,@.objecttype varchar(32)

,@.encrypted nvarchar(32)

,@.pass_through_scripts nvarchar(max)

,@.eventDoc int

,@.db_name sysname

,@.targetobject nvarchar(51)

set @.targetobject=N''

-- parse event data

select @.object_name = event_instance.value('ObjectName[1]', 'sysname')

,@.object_owner = event_instance.value('SchemaName[1]', 'sysname')

,@.objecttype = event_instance.value('ObjectType[1]', 'varchar(32)')

,@.encrypted = event_instance.value('(TSQLCommand/SetOptions/@.ENCRYPTED)[1]', 'nvarchar(32)')

,@.pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')

,@.targetobject = event_instance.value('TargetObjectName[1]', 'nvarchar(512)')

FROM @.EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

select @.qual_object_name = QUOTENAME(@.object_owner) + N'.' + QUOTENAME(@.object_name)

select @.objid = object_id(@.qual_object_name)

select @.db_name=db_name()

select @.pass_through_scripts = sys.fn_replgetparsedddlcmd(@.pass_through_scripts

,N'ALTER'

,@.objecttype

,@.db_name

,@.object_owner

,@.object_name

,@.targetobject)

if UPPER(@.objecttype) != N'TABLE' and UPPER(@.objecttype) != N'TRIGGER'

begin

select @.pass_through_scripts = N'ALTER ' + @.objecttype + N' '

+ @.qual_object_name + N' '

+ @.pass_through_scripts

end

If (@.procmapid = 1)

begin

IF(@.object_name in (Select Article from MSsubscription_articles))

begin

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

Else

begin

Commit Transaction

Print 'Transaction Commited!!!!!'

end

end

end

GO

Kindly check and let me know.

Thanks and Regards,

Swapna.B.

|||

Try removing 'COMMIT TRANSACTION' from the second BEGIN END block at the end of your stored proc, see below, leave ROLLBACK TRANSACTION in place.

Chris

If (@.procmapid = 1)

BEGIN

IF(@.object_name in (SELECT Article from MSsubscription_articles))

BEGIN

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

--Else

--BEGIN

--Commit Transaction

--Print 'Transaction Commited!!!!!'

--end

end

DDL Trigger...

Hi,

I have a scenario in which i need to restrict the schema changes of around 5 tables in a database. If I use ON Database option of DDL Trigger, the restriction is imposed on the entire database. But the requirement is retricting only 5 tables in the database, since other tables will undergo some schame changes in the future.

Is there anyway I could achieve this requirement?

Thanks,

Swapna.B.

Take a look into the eventdata function. It can be used within your code to scope the restriction to a specific set of tables.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/675b8320-9c73-4526-bd2f-91ba42c1b604.htm

|||

Hi,

I have achieved the requirement using eventdata function and validating the values in a separate sp. Thanks for the help provided.

I have the DDL trigger which calls a stored procedure. This SP does the segregating of values from eventdata function , validating those values and commits or rollsback according to the objectname retrieved. Now its working fine. But i face one error like the one below.

When ever the DDL Trigger is executed, it throws an error like

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The transactions are getting completed successfully but this error occurs everytime we try to make changes to any table in the database.

What is the reason for this error? Kindly let me know the way to avoid it.

One more point here is when we try to execute a batch of alter statements without go command in the database in which the DDL trigger is present, Only the first one gets executed other statements are not getting considered. Is this anyway related to the error above?

Thanks and Regards,

Swapna.B.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes to
tables in a database that are owned by dbo. However, I do not want this user to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the user to
create, delete, and alter objects owned by themselves. And, the only way to get
what I want is to add the user to the db_owner fixed database role. Not really
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrell
db_ddladmin can alter and drop object owned by others.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema changes to tables in a
> database that are owned by dbo. However, I do not want this user to do anything beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by themselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database role. Not really what I
> had in mind. Am I missing something here? Can anybody give me any direction on this?
> Thanks in advance. You guys rock!
> Darrell
|||db_ddladmin is able to modify all tables in the database. Use Query Analyzer
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:

> Ok, folks, I got one for you. I want to allow a user to make schema changes to
> tables in a database that are owned by dbo. However, I do not want this user to
> do anything beyond that, such as add roles or change permissions on objects,
> etc. It appears that the db_ddladmin fixed database role only allows the user to
> create, delete, and alter objects owned by themselves. And, the only way to get
> what I want is to add the user to the db_owner fixed database role. Not really
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>
|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analyzer
> to alter tables instead of Enterprise Manager if you don't want to see those
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestion,
however, I have a bunch of GUI-loving developers that probably couldn't spell
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes
to
tables in a database that are owned by dbo. However, I do not want this user
to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the use
r to
create, delete, and alter objects owned by themselves. And, the only way to
get
what I want is to add the user to the db_owner fixed database role. Not real
ly
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrelldb_ddladmin can alter and drop object owned by others.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema change
s to tables in a
> database that are owned by dbo. However, I do not want this user to do any
thing beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db
_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by th
emselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database r
ole. Not really what I
> had in mind. Am I missing something here? Can anybody give me any directio
n on this?
> Thanks in advance. You guys rock!
> Darrell|||db_ddladmin is able to modify all tables in the database. Use Query Analyze
r
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:

> Ok, folks, I got one for you. I want to allow a user to make schema change
s to
> tables in a database that are owned by dbo. However, I do not want this us
er to
> do anything beyond that, such as add roles or change permissions on object
s,
> etc. It appears that the db_ddladmin fixed database role only allows the u
ser to
> create, delete, and alter objects owned by themselves. And, the only way t
o get
> what I want is to add the user to the db_owner fixed database role. Not re
ally
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analy
zer
> to alter tables instead of Enterprise Manager if you don't want to see tho
se
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestio
n,
however, I have a bunch of GUI-loving developers that probably couldn't spel
l
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes to
tables in a database that are owned by dbo. However, I do not want this user to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the user to
create, delete, and alter objects owned by themselves. And, the only way to get
what I want is to add the user to the db_owner fixed database role. Not really
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrelldb_ddladmin can alter and drop object owned by others.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema changes to tables in a
> database that are owned by dbo. However, I do not want this user to do anything beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by themselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database role. Not really what I
> had in mind. Am I missing something here? Can anybody give me any direction on this?
> Thanks in advance. You guys rock!
> Darrell|||db_ddladmin is able to modify all tables in the database. Use Query Analyzer
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:
> Ok, folks, I got one for you. I want to allow a user to make schema changes to
> tables in a database that are owned by dbo. However, I do not want this user to
> do anything beyond that, such as add roles or change permissions on objects,
> etc. It appears that the db_ddladmin fixed database role only allows the user to
> create, delete, and alter objects owned by themselves. And, the only way to get
> what I want is to add the user to the db_owner fixed database role. Not really
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analyzer
> to alter tables instead of Enterprise Manager if you don't want to see those
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestion,
however, I have a bunch of GUI-loving developers that probably couldn't spell
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS

dbo schema added when i rename table

i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05

my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one

1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.

DROP TABLE aMgmt.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'
GO

any help?Unclear what is happening or what you are trying to do.

What did you use to record the script? What was the original ownership on the tables? What were you logged in as when you scripted the objects?

Who do you WANT to have ownership of the objects? I assume dbo...|||It may be becoz u r trying to copy/create the data using a dbo user.

DBO Schema

I understand there are advantages to creating schemas rather than using the
default dbo schema, but are there any good reasons not to use dbo? Are ther
e
any white papers out there that talk about advantages and disadvantages?
Thanks,
MitchMitch (Mitch@.discussions.microsoft.com) writes:
> I understand there are advantages to creating schemas rather than using
> the default dbo schema, but are there any good reasons not to use dbo?
> Are there any white papers out there that talk about advantages and
> disadvantages?
Let me put it this way: as long as you think one namespace is all you
need, stick with dbo. And there are many situations where one namespace is
OK.
But if the database is big, and there are several teams working
independently, there is a very good idea to have separate schemas to
avoid clashes.
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|||The question you should ask instead should be: do you have any good reasons
for using the dbo schema? If not, then you should not use it.
Using different schemas owned by different principals helps you separate the
objects contained in them and provides better security for those objects,
because if someone compromises one schema, he'll have a harder time getting
at the data in other schemas. So, schemas can be used for defense in depth.
Using the dbo schema is particularly bad because it is owned by dbo and
you'll have to grant permissions on objects owned by dbo, which, if
compromised, could allow an attacker to execute code with the permissions of
dbo. By not using the dbo schema you are following the principle of minimal
privileges.
If you choose to use the dbo schema instead of different schemas owned by
different principals, then you are avoiding to enforce two significant
security principles.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:F727A8E0-533B-4625-8175-BF839366F5E3@.microsoft.com...
>I understand there are advantages to creating schemas rather than using the
> default dbo schema, but are there any good reasons not to use dbo? Are
> there
> any white papers out there that talk about advantages and disadvantages?
> Thanks,
> Mitch|||Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@.nospam.com) writes:
> The question you should ask instead should be: do you have any good
> reasons for using the dbo schema? If not, then you should not use it.
> Using different schemas owned by different principals helps you separate
> the objects contained in them and provides better security for those
> objects, because if someone compromises one schema, he'll have a harder
> time getting at the data in other schemas. So, schemas can be used for
> defense in depth.
I would question that this a useful approach in all cases. For the
system I work with, using schemas would make very much sense, since
we have divided the systems into subsystem, so using schemas could
give each subsystem its own namespace. If we ever take that route
remains to see, but if we ever do it is also clear that all schemas
would be owned by dbo. Anything else would only cause problems with
broken ownership chains and descreased security. Today we grant all
users SELECT on all tables since we some dynamic SQL here and there.
But some of customers do not really like that, and we should abandon
it. But if subsystem schemas would have different owners, ownership
chaining would break (outer subsystems frequently refer to inner
subsystems), which would requires users to also have INSERT, UPDATE
and DELETE privs which is completely unacceptable. Or we would have
to entangle in a web of certificates and procedure signing.
There may of course be situations where it may make sense to have
different owners of objects in a database, but I will have to admit
that I can't really envision such a scenario.
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|||In this case, there will be one database user, but there will also be about
700 tables. I was thinking it would be useful to have related tables groupe
d
into different schemas to give a visual relation at first glance.
Also, the user now has db_owner role, but I really don't think it needs to
have more than read/write.
"Erland Sommarskog" wrote:

> Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@.nospam.com) writes:
> I would question that this a useful approach in all cases. For the
> system I work with, using schemas would make very much sense, since
> we have divided the systems into subsystem, so using schemas could
> give each subsystem its own namespace. If we ever take that route
> remains to see, but if we ever do it is also clear that all schemas
> would be owned by dbo. Anything else would only cause problems with
> broken ownership chains and descreased security. Today we grant all
> users SELECT on all tables since we some dynamic SQL here and there.
> But some of customers do not really like that, and we should abandon
> it. But if subsystem schemas would have different owners, ownership
> chaining would break (outer subsystems frequently refer to inner
> subsystems), which would requires users to also have INSERT, UPDATE
> and DELETE privs which is completely unacceptable. Or we would have
> to entangle in a web of certificates and procedure signing.
> There may of course be situations where it may make sense to have
> different owners of objects in a database, but I will have to admit
> that I can't really envision such a scenario.
>
> --
> 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
>|||It's definitely not the necessary approach in all cases - you can have
scenarios where this may not be needed. But without a specific scenario
being the subject of discussion, I made the safest geneal recommendation.
I see ownership chaining as a sword with two edges - it is desirable in some
scenarios, but not in others, hence I recommended breaking it intentionally
accross schemas.
We always have to make tradeoffs between security, manageability, and
performance. Because the questions are posted on the security forum, I
emphasize security in my answers ;)
In the end, the most important thing is to be aware of what tradeoffs are
made in a design. There is no substitute for understanding what you're
building.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BC14195D92Yazorman@.127.0.0.1...
> Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@.nospam.com) writes:
> I would question that this a useful approach in all cases. For the
> system I work with, using schemas would make very much sense, since
> we have divided the systems into subsystem, so using schemas could
> give each subsystem its own namespace. If we ever take that route
> remains to see, but if we ever do it is also clear that all schemas
> would be owned by dbo. Anything else would only cause problems with
> broken ownership chains and descreased security. Today we grant all
> users SELECT on all tables since we some dynamic SQL here and there.
> But some of customers do not really like that, and we should abandon
> it. But if subsystem schemas would have different owners, ownership
> chaining would break (outer subsystems frequently refer to inner
> subsystems), which would requires users to also have INSERT, UPDATE
> and DELETE privs which is completely unacceptable. Or we would have
> to entangle in a web of certificates and procedure signing.
> There may of course be situations where it may make sense to have
> different owners of objects in a database, but I will have to admit
> that I can't really envision such a scenario.
>
> --
> 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|||In this case, there will be about 700 tables. I was thinking it would be
nice to group related tables by schema to quickly see the groupings.
Also, there is only 1 actual database user. It currently has db_owner, but
I believe it only needs read/write privileges.
"Laurentiu Cristofor [MSFT]" wrote:

> It's definitely not the necessary approach in all cases - you can have
> scenarios where this may not be needed. But without a specific scenario
> being the subject of discussion, I made the safest geneal recommendation.
> I see ownership chaining as a sword with two edges - it is desirable in so
me
> scenarios, but not in others, hence I recommended breaking it intentionall
y
> accross schemas.
> We always have to make tradeoffs between security, manageability, and
> performance. Because the questions are posted on the security forum, I
> emphasize security in my answers ;)
> In the end, the most important thing is to be aware of what tradeoffs are
> made in a design. There is no substitute for understanding what you're
> building.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns98BC14195D92Yazorman@.127.0.0.1...
>
>|||Mitch (Mitch@.discussions.microsoft.com) writes:
> In this case, there will be one database user, but there will also be
> about 700 tables. I was thinking it would be useful to have related
> tables grouped into different schemas to give a visual relation at first
> glance.
So was your question aksed from a security perspective, or from a
modularisation perspective?
Yes, with 700 tables multiple schemas can be a good idea, as it may
make the data model easier to understand and approach.
Also, another advantage with using different schemas, is that it
becomes natural to always use two-part notation. There are situations
where this improves performance.

> Also, the user now has db_owner role, but I really don't think it needs to
> have more than read/write.
I suppose this single database user will be a proxy for a lot of real
users? Yes, this user should definitely not have more rights than
necessary. Ideally, you should use stored procedures and all the
user would need is execution rights on the schemas. If you need to
use dynamic SQL in some places, this is best addressed with signing
these particular procedures with certificate.
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