Showing posts with label app. Show all posts
Showing posts with label app. Show all posts

Thursday, March 22, 2012

best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyC
ontacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to th
e
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompan
yContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:

> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedur
e
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:[vbcol=seagreen]
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
>|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:

>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompany
Contacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!

>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.

> Since I
>didn't want to give insert, update or delete permissions on any tables to t
he
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.
--
Dan D.
"Hugo Kornelis" wrote:

> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
>
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
>
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fir
e
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:

> The trigger they wrote also contained a rookie mistake. It was designed fo
r
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This co
de
> will raise an error if there is more than one row in the Inserted table (i
n
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or inser
t
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in the
ir
> procs. If you do put it in your procs, you will need to put that logic int
o
> every proc that will update or insert into that table. If this is a large
#
> of procs then you are setting up a shotgun surgery approach to maintenance
.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want t
he
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...[vbcol=seagreen]
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under contro
l.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, th
e
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:

> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
>
>

best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to the
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
--
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:
> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedure
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > database.
> > I've tried to keep all access via stored procedures and views and the user
> > only has execute permissions on the sp and select on the views.
> >
> > The contractors have put a trigger on one of the tables.
> >
> > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > FOR INSERT, UPDATE
> > AS
> > IF (Select bitCompPrimaryContact From Inserted) = 1
> > Begin
> > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > Where numCompanyId = (Select numCompanyId From Inserted)
> > And numContactID <> (Select numContactId From Inserted)
> > End
> >
> > So, the user needs select and update permissions on the table. Since I
> > didn't want to give insert, update or delete permissions on any tables to
> > the
> > user is there a way around it? What is a best practice approach. Do I just
> > not use update triggers?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
> > As long as you are using stored procedures you do not need to use this
> > trigger. Tell the contractors to put this code within the stored procedure
> > that updates the table tblCompanyContacts.
> >
> > --
> > Keith Kratochvil
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > > database.
> > > I've tried to keep all access via stored procedures and views and the user
> > > only has execute permissions on the sp and select on the views.
> > >
> > > The contractors have put a trigger on one of the tables.
> > >
> > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > > FOR INSERT, UPDATE
> > > AS
> > > IF (Select bitCompPrimaryContact From Inserted) = 1
> > > Begin
> > > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > > Where numCompanyId = (Select numCompanyId From Inserted)
> > > And numContactID <> (Select numContactId From Inserted)
> > > End
> > >
> > > So, the user needs select and update permissions on the table. Since I
> > > didn't want to give insert, update or delete permissions on any tables to
> > > the
> > > user is there a way around it? What is a best practice approach. Do I just
> > > not use update triggers?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> >
> >
> >|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!
>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.
> Since I
>didn't want to give insert, update or delete permissions on any tables to the
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.:)
--
Dan D.
"Hugo Kornelis" wrote:
> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
> >Using SS2000 SP4. We have a .NET app that accesses the sql server database.
> >I've tried to keep all access via stored procedures and views and the user
> >only has execute permissions on the sp and select on the views.
> >
> >The contractors have put a trigger on one of the tables.
> >
> >CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> >FOR INSERT, UPDATE
> >AS
> >IF (Select bitCompPrimaryContact From Inserted) = 1
> >Begin
> >Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >Where numCompanyId = (Select numCompanyId From Inserted)
> >And numContactID <> (Select numContactId From Inserted)
> >End
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
> >So, the user needs select and update permissions on the table.
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
> > Since I
> >didn't want to give insert, update or delete permissions on any tables to the
> >user is there a way around it? What is a best practice approach. Do I just
> >not use update triggers?
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fire
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:
> The trigger they wrote also contained a rookie mistake. It was designed for
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This code
> will raise an error if there is more than one row in the Inserted table (in
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or insert
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in their
> procs. If you do put it in your procs, you will need to put that logic into
> every proc that will update or insert into that table. If this is a large #
> of procs then you are setting up a shotgun surgery approach to maintenance.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want the
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>> The trigger they wrote also contained a rookie mistake. It was designed
>> for
>> a single row at a time. When you are writing a trigger you always need to
>> plan on having multi row events occur at some point.
>> Snip--
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> Where numCompanyId = (Select numCompanyId From Inserted)
>> And numContactID <> (Select numContactId From Inserted)
>> End snip--
>> See how the subqueries assume that only one item will be returned. This
>> code
>> will raise an error if there is more than one row in the Inserted table
>> (in
>> other words, if a multi row update or insert occurs) and that would cause
>> the original transaction to be rolled back.
>> Far better would be the following:
>> ---
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> FROM tblCompanyContacts C
>> JOIN Inserted I
>> ON I.numCompanyId = C.numCompanyId
>> And I.numContactID <> C.numContactId
>> ---
>> This way no matter how many rows have been affected by the update or
>> insert
>> statement the trigger will now handle them.
>> All of the advice you received on moving the logic to the stored
>> procedure
>> has merit. You should understand the following. The trigger will fire for
>> any update or insert on your table. Some folks like to use triggers to
>> handle some of their data integrity enforcement and not worry about in
>> their
>> procs. If you do put it in your procs, you will need to put that logic
>> into
>> every proc that will update or insert into that table. If this is a large
>> #
>> of procs then you are setting up a shotgun surgery approach to
>> maintenance.
>> The other thing to be aware of is that your trigger fires as part of your
>> original transaction, so an error in it will cause your original action
>> to
>> be rolled back, as well as the locks from your original action are still
>> maintained during the trigger. In this case that is not a bad thing,
>> since
>> you want this to happen as part of the update, and if it fails, you want
>> the
>> update to fail as well.
>> --
>> David Lundell
>> Principal Consultant and Trainer
>> www.MutuallyBeneficial.com
>> David@.MutuallyBeneficial.com
>>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under control.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, the
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:
> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> > So, is it probably safer to stay away from subqueries in triggers?
> >
> > One of the things we have to be careful about with triggers is that we do
> > a
> > lot of data manipulation on the backend. So we have to be sure that if we
> > insert 5M rows on the backend and not through the app, a trigger doesn't
> > fire
> > and do something it shouldn't.
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "David Lundell" wrote:
> >
> >> The trigger they wrote also contained a rookie mistake. It was designed
> >> for
> >> a single row at a time. When you are writing a trigger you always need to
> >> plan on having multi row events occur at some point.
> >>
> >> Snip--
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> Where numCompanyId = (Select numCompanyId From Inserted)
> >> And numContactID <> (Select numContactId From Inserted)
> >> End snip--
> >>
> >> See how the subqueries assume that only one item will be returned. This
> >> code
> >> will raise an error if there is more than one row in the Inserted table
> >> (in
> >> other words, if a multi row update or insert occurs) and that would cause
> >> the original transaction to be rolled back.
> >>
> >> Far better would be the following:
> >> ---
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> FROM tblCompanyContacts C
> >> JOIN Inserted I
> >> ON I.numCompanyId = C.numCompanyId
> >> And I.numContactID <> C.numContactId
> >> ---
> >> This way no matter how many rows have been affected by the update or
> >> insert
> >> statement the trigger will now handle them.
> >>
> >> All of the advice you received on moving the logic to the stored
> >> procedure
> >> has merit. You should understand the following. The trigger will fire for
> >> any update or insert on your table. Some folks like to use triggers to
> >> handle some of their data integrity enforcement and not worry about in
> >> their
> >> procs. If you do put it in your procs, you will need to put that logic
> >> into
> >> every proc that will update or insert into that table. If this is a large
> >> #
> >> of procs then you are setting up a shotgun surgery approach to
> >> maintenance.
> >> The other thing to be aware of is that your trigger fires as part of your
> >> original transaction, so an error in it will cause your original action
> >> to
> >> be rolled back, as well as the locks from your original action are still
> >> maintained during the trigger. In this case that is not a bad thing,
> >> since
> >> you want this to happen as part of the update, and if it fails, you want
> >> the
> >> update to fail as well.
> >> --
> >> David Lundell
> >> Principal Consultant and Trainer
> >> www.MutuallyBeneficial.com
> >> David@.MutuallyBeneficial.com
> >>
> >>
> >>
>
>|||On Tue, 23 May 2006 06:07:02 -0700, Dan D. wrote:
>So, is it probably safer to stay away from subqueries in triggers?
Hi Dan,
Only in the same way that not using a database at all prevents errors :)
Use subqueries if you need them - just be aware that a trigger will
always fires exactly once when a corresponding INSERT, UPDATE or DELETE
statement is executed. Design your triggers to work regardless of number
of rows affected. And test them with single-row, multi-row and zero-row
operations.
>One of the things we have to be careful about with triggers is that we do a
>lot of data manipulation on the backend. So we have to be sure that if we
>insert 5M rows on the backend and not through the app, a trigger doesn't fire
>and do something it shouldn't.
Bottom line when deciding if something belongs in a stored proc or in a
trigger is: is in integrity related?
Triggers should be used to enforce the integrity rules that can't be
enforced with standard SQL. For instance complicated constraints that
can't be exppressed in SQL constraints. Or computations that (for
whatever reason) can not be calculated in a view. The execution of a
trigger is about as hard to bypass as the checking of a constraint or
the "updating" of a calculated value in a view. That's because it should
be used for code that really should never be bypassed.
Bypassing the execution of stored procedures is much easier. Exactly how
easy depends on yoour security model as well - but there are always SOME
users able to update a table directly without executing the stored proc
that should be used to update the table.
--
Hugo Kornelis, SQL Server MVP

Tuesday, March 20, 2012

Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph

Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
Look into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas

"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph

Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph

Best Practices - SQL Transactions

I have a web app (ASP) that does all updates, inserts by calling
transaction-supported COM+ components (with the transaction started in the
ASP page, i.e. transaction=required) that use ADO to call stored procedures
(that usually involve single tables). If there is any error (missing SP,
parameter value of wrong type, etc.) with the database insert/update, MTS
automatically rolls back everything that was done in the database (and maybe
SQL Server does that with any error in an SP anyway'). As such, when I
write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
So, I have many SPs (that do not return any indication of success, or not)
like
INSERT INTO Table
(ColumnA, ColumnB)
VALUES
(ValueA, ValueB)
WHERE
Some condition
As a matter of best practice, should SQL programmers always enclose INSERTs,
UPDATEs within transactional statements in a production database? Should one
always check for errors with INSERTS or UPDATES? Or, with errors like I
describe (but not with business logic), does SQL Server automatically
rollback everything in an SP? Or, should one just save those statements for
when the SQL script and logic itself takes care of rolling back a database
when a series of updates or inserts are made?
Thanks for any thoughts."Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES?
No,

>Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP?
No, but the client will get an error message and rollback.
Usually, stored procedures should not contain transactional logic. Let the
client take care of it.
David|||Don Miller wrote:
> As a matter of best practice, should SQL programmers always enclose INSERT
s,
> UPDATEs within transactional statements in a production database? Should o
ne
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements fo
r
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
My thoughts are:
1) Transactions are only required if there's more than one DML
statement (or SELECT statement that needs to maintain a lock).
2) Error checking should be done after any statement that can fail.
This includes all DML and DDL statements. Pretty much everything except
SELECTs (though I guess they could technically fail as well...).
Kris|||David wrote:
> Usually, stored procedures should not contain transactional logic. > Let the clie
nt take care of it.
Are you sure? Isn't it better to ensure your stored procedures are
transactionally correct regardless of where they are executed from?
Kris|||To my knowledge, an error is not sufficient for MTS to roll back: if you do
not call ObjectContext.SetAbort explicitely, MTS will think that the whole
operation has been successfull and will commit it; even if there have been
one or multiple errors.
In the same way, if there is an error inside a SP, SQL-Server will not
rollback the transaction automatically for you: you must check for any error
(@.@.error) and call the rollback operation yourself.
Even if the SP is already enrolled in a transaction, you still need to check
for a transaction error (@.@.error) if there is such a possibility inside the
SP and this, even if you have not included a BEGIN TRAN inside it.
To be clear, transactions and errors are not same: the fact that there have
been an error doesn't mean that the transaction will be or need to be
aborted.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
> for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>|||Don Miller (nospam@.nospam.com) writes:
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs, UPDATEs within transactional statements in a production
> database? Should one always check for errors with INSERTS or UPDATES?
> Or, with errors like I describe (but not with business logic), does SQL
> Server automatically rollback everything in an SP? Or, should one just
> save those statements for when the SQL script and logic itself takes
> care of rolling back a database when a series of updates or inserts are
> made?
If it's a single statement, there is no reason to have BEGIN/COMMIT
TRANSACTION around it, since the statement is a transaction in itself.
However, if you procedure performs several INSERT/UPDATE/DELETE statements
there should be a transaction around it. The procedure should not rely on
that the caller has set up a transaction. Sometimes you have a procedure
that you know is only performing part of a game. In this case, it is a
good habit to have this in the beginning:
IF @.@.trancount = 0
BEGIN
RAISERROR ('This procedure must be called with an active transaction',
16, 1)
RETURN 1
END
In SQL 2005, error checking in stored procedures can be handled with
TRY-CATCH. In SQL 2000, you need to check @.@.error, and if you have
started a transaction, you should rollback, since you know that you
were not able to fulfil your contract.
I have two articles on error handling in SQL Server on my web site.
http://www.sommarskog.se/error-handling-II.html gives more suggestions
on implementing error handling.
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 the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
The exception is when SET XACT_ABORT ON is active on the connection or proc.
SQL Server will then rollback the transaction and abort the batch when
runtime errors are encountered. However, compile errors are not affected
with XACT_ABORT ON so @.@.ERROR still needs to be checked when you want to
safeguard against all errors.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uUuZ5PNbGHA.1536@.TK2MSFTNGP02.phx.gbl...
> To my knowledge, an error is not sufficient for MTS to roll back: if you
> do not call ObjectContext.SetAbort explicitely, MTS will think that the
> whole operation has been successfull and will commit it; even if there
> have been one or multiple errors.
> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
> Even if the SP is already enrolled in a transaction, you still need to
> check for a transaction error (@.@.error) if there is such a possibility
> inside the SP and this, even if you have not included a BEGIN TRAN inside
> it.
>
> To be clear, transactions and errors are not same: the fact that there
> have been an error doesn't mean that the transaction will be or need to be
> aborted.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>|||<kriskirk@.hotmail.com> wrote in message
news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
> David wrote:
> Are you sure? Isn't it better to ensure your stored procedures are
> transactionally correct regardless of where they are executed from?
>
Ideally yes. Stored procedures should be atomic, consistent, and isolated.
They should typically not be durable because that makes assumptions about
where the procedure fits inside user transactions. But it requires quite a
bit of transaction handling code to make that happen.
Here's an example. A stored procedure should almost never issue a ROLLBACK
except to a savepoint. If it does then it can't be called in the scope of
an existing transaction. That might be OK for administrative stuff that you
know will be run from Management Studio, but for regular database
transactions.
create procedure foo
as
begin
begin transaction foo
begin try
'do work here
commit transaction
end try
begin catch
rollback transaction foo
commit transaction
exec usp_reraise_error
end catch
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e4G3seSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> <kriskirk@.hotmail.com> wrote in message
> news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
>
> Ideally yes. Stored procedures should be atomic, consistent, and
> isolated. They should typically not be durable because that makes
> assumptions about where the procedure fits inside user transactions. But
> it requires quite a bit of transaction handling code to make that happen.
> Here's an example. A stored procedure should almost never issue a
> ROLLBACK except to a savepoint. If it does then it can't be called in the
> scope of an existing transaction. That might be OK for administrative
> stuff that you know will be run from Management Studio, but for regular
> database transactions.
>
Oops, here's a correction after morinng coffee.
create procedure foo
as
begin transaction
save transaction proc_scope
begin try
--DO WORK HERE
commit transaction
end try
begin catch
rollback transaction proc_scope
commit transaction
declare @.errormessage nvarchar(4000),
@.errorseverity int
select
@.errormessage = error_message(),
@.errorseverity = error_severity()
raiserror(@.errormessage, @.errorseverity, 1)
end catch
In SQL 2005 you can just cut and paste all this junk around your procedure,
and you don't have to pollute the implementation with a bunch of error
handling noise.
So there is a right way to do transaction handling in a stored proceudre,
and it isn't all that hard, but transacaction handling is basically the
responsibility of the client code.
David|||Thanks to all who have responded, although I'm still not quite sure whether
I should (or need to) revisit about 100 SPs I have (that are called from a
client transaction through COM+ - and yes, the client does the
ObjectContext.SetAbort duties). It does work today with rollbacks as
necessary and expected but I felt lazy by relying on ASP to start the
transaction and have the MTS blackbox take care of the details especially
when dealing with SQL Server. But I guess that's a feature ;)
And thanks to Erland Sommarskog for the very thoughtful piece about error
handling.
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
> I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
INSERTs,
> UPDATEs within transactional statements in a production database? Should
one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>

Wednesday, March 7, 2012

Best option to update every Jan 1st

I have a vacation request app I'm designing, and it has a VacationData Table with TotalVac, UsedVac, VacLeft, VacationCarriedOver, and VacCompleted.

I need to take the VacLeft and divide by 2 and place that data in the following two spots, one in the VCO and add it to the TotalVac, which is pulled from another table w/hire date and other info.

I only need to run this on Jan 1 of every year, any suggestions?

Write a console application and run it as a scheduled task.|||

You can schedule a job to run on SQL Server using the SQL Agent on Jan 1. You can also schedule it through the command-line "AT" command. A third option is to detect the year change in your application like in session start. A fourth option is to detect it in the stored procedures you are using to retrieve that data.

Take your pick.

IMHO however, I suspect it would probably be best to track when vacation days are earned, and when they are spent. Having this data already, those two fields are now redundant, but could be emulated as computed fields using a user defined function.

Friday, February 24, 2012

Best approach to sending field names dynamically

Hi,

I have a C# web app that searches my database table using the
following search parameters

Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?

Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.

If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Thank you
Maz.(maflatoun@.gmail.com) writes:

Quote:

Originally Posted by

I have a C# web app that searches my database table using the
following search parameters
>
Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?
>
Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.
>
If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.


Yes, these sort of searches are not very easy to do with static SQL.
I have an article on my web site that discusses this topic in detail:
http://www.sommarskog.se/dyn-search.html.

--
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

Sunday, February 12, 2012

Beginner- Need Help With SQL Server 2005 Express

I am an absolute beginner with SQL Server, and I have now developed an app that needs to store, read, and search for saved data. Basically, my program will have to search through the databse to find a particular URL match to a string I supply, and then retrieve the Username and Password data that is associated with that URL, storing those to strings.

My problem is, I've learned how to create a database and tables in the IDE, but all of the examples on the web that I've found only talk about using SQL Server with Visual Basic 2005 in uses such as databinding to controls and displaying the database in datagrids and such on the form. I need help with how to interact with the database in code, seeing as the user of the program will likely never actually see the database contents- the data will be for the program's use only.

So, I was wondering if anyone could provide/guide me to some helpful information on interacting with SQL databases through code in VB Express 2005.

Thanks for any help.

In your VB code, define variables, and then instead of assigning data to control.text, assign data to the variables.

|||OK, but then how do I search through all of the fields in 1 column, and then if I find the right one, extract the adjacent columns for that field and save them as strings?