Row-level Security: Permissions required on base table?

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

Row-level Security: Permissions required on base table?

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

sql

row level security in SQL

Hi Gurus, I am looking for row level security in sql
server. I was told that sql enterprise edtion is required
to implement this. can anyone give me some direction of
how this can be done? thanks.This is a multi-part message in MIME format.
--=_NextPart_000_00B3_01C37844.E0892760
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't need EE. Rather, you can create views and grant access to the =views. For example, if you wanted to restrict people's access to just =the rows they added - and you were tracking that information - then you =could have:
create view MyView
as
select
*
from
MyTable
where
CreateUser =3D CURRENT_USER
go
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"yl" <hubrian@.hotmail.com> wrote in message =news:16c601c37865$143b3380$a601280a@.phx.gbl...
Hi Gurus, I am looking for row level security in sql server. I was told that sql enterprise edtion is required to implement this. can anyone give me some direction of how this can be done? thanks.
--=_NextPart_000_00B3_01C37844.E0892760
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need EE. Rather, you =can create views and grant access to the views. For example, if you wanted to =restrict people's access to just the rows they added - and you were =tracking that information - then you could have:
create view MyView
as
select
=*
from
=MyTable
where
CreateUser ==3D CURRENT_USER
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"yl" wrote in message news:16c601c37865$14=3b3380$a601280a@.phx.gbl...Hi Gurus, I am looking for row level security in sql server. I was told =that sql enterprise edtion is required to implement this. can anyone give =me some direction of how this can be done? thanks.

---

--=_NextPart_000_00B3_01C37844.E0892760--|||thank you all very much.
>--Original Message--
>Hi Gurus, I am looking for row level security in sql
>server. I was told that sql enterprise edtion is
required
>to implement this. can anyone give me some direction of
>how this can be done? thanks.
>.
>

Row by Row Copy

I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:

1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...

2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.

The latter solution would be the most elegant, but I'll take what I can get...

how about this?

--

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Syntax

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:

--(1)

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employees')

DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

fname AS first,

minit AS middle,

lname AS last,

IDENTITY(smallint, 100, 1) AS job_num,

job_lvl AS job_level,

pub_id,

hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

|||

What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.

|||

I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:

old_ID new_ID

4 227

63 228

65 229

I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).

So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.

Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...

|||

Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:

create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;

declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;

select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;

commit;

select * from T;

drop table T;

The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.

|||

Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.

Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?

Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:

1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!

2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.

Even if you concur with either of the above strategies, is there an alternative you prefer?

|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||

I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.

So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.

Row by Row Copy

I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:

1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...

2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.

The latter solution would be the most elegant, but I'll take what I can get...

how about this?

--

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Syntax

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:

--(1)

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employees')

DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

fname AS first,

minit AS middle,

lname AS last,

IDENTITY(smallint, 100, 1) AS job_num,

job_lvl AS job_level,

pub_id,

hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

|||

What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.

|||

I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:

old_ID new_ID

4 227

63 228

65 229

I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).

So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.

Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...

|||

Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:

create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;

declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;

select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;

commit;

select * from T;

drop table T;

The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.

|||

Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.

Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?

Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:

1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!

2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.

Even if you concur with either of the above strategies, is there an alternative you prefer?

|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||

I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.

So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.