DML Trigger

Question

Post reply

DML Trigger

demin99

SSC Eights!

Points: 927
More actions
June 21, 2012 at 11:22 am

#262755

Iam trying to create a trigger on a table so that only certain users will be allowed to execute
insert update and delete on that particular table.
I have this in place but this blocks all users.Is there anyway I can allow only 2-3 users to execute these(insert,update,delete) operations
create trigger tab1tr on dbo.tab1
for delete,update,insert
as raiseerror ('Operation not allowed')
Thanks for your help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ok, what you want is possible, but it's the wrong way to do it;

access should be handled via roles, and not at the dml trigger.

it's much, much easier to create some roles that take away the INSERT/UPDATE/DELETE on a specific table, and add the users who should not be doing the updates:

USE [SandBox]

GO

CREATE ROLE [NoUpdateForYou]

GO

use [SandBox]

GO

DENY DELETE ON [dbo].[Locations] TO [NoUpdateForYou]

GO

use [SandBox]

GO

DENY INSERT ON [dbo].[Locations] TO [NoUpdateForYou]

GO

use [SandBox]

GO

DENY UPDATE ON [dbo].[Locations] TO [NoUpdateForYou]

GO

then you add the persons whos should not ber touching it to the group.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Why do you want to do this via triggers? Why not handle it with standard permissions? Only allow select to other users and allow insert/update/delete to the role these other users belong to.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Looks like Lowell and I were typing at the same time. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 4

Sean Lange (6/21/2012)
Looks like Lowell and I were typing at the same time. 🙂

yeah but your answer is actually better; least amount of access is the best;

my example was for fixing the barn-door-is-open after the horse escapes;

i was assuming too much access and trimming certain people back, but assuming things like a blanket db_datawriter was in place.

as usual, it depends ont he details for which is best, but at least he's seeing two options!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Lowell (6/21/2012)
Sean Lange (6/21/2012)
Looks like Lowell and I were typing at the same time. 🙂
yeah but your answer is actually better; least amount of access is the best;
my example was for fixing the barn-door-is-open after the horse escapes;
i was assuming too much access and trimming certain people back, but assuming things like a blanket db_datawriter was in place.
as usual, it depends ont he details for which is best, but at least he's seeing two options!

But the tone of yours is much nicer. You attempted to teach and provided examples while I just told them what they did wrong. Something I need to work on. 😎

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

demin99 SSC Eights! Points: 927 More actions · Answer 6

Thanks Sean & Lowell for your help . Let me try this 🙂