restrict update column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

restrict update column

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-06-06 : 04:55:37

Hello everyone,
Could anyone tell me how can i restrict user to update columns on a table in sql server.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-06 : 05:24:59

You can grant column level permissions - think that came in with v2008 but could be wrong
Trigger?
If you don't want them to be ablle to query the other columns either then a view.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-06-06 : 06:10:18

Thanx for the reply.
Can we write a custom trigger to restrict updates on a column??

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-06 : 06:30:22

yes

create trigger tr_tbl on tbl for update
as

if update (mycolumn)
begin
if exists
(
select * from inserted i
join updated u
on i.pk = u.pk
and (i.mycolumn <> u.mycolumn or (i.mycolumn is null and u.mycolumn is not null) or (i.mycolumn is not null and u.mycolumn is null))
)
begin
rollback tran
raiserror ('cannot update column', 16, -1)
end
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-06 : 16:49:10

quote:
Originally posted by nigelrivett

yes

create trigger tr_tbl on tbl for update
as

if update (mycolumn)
begin
if exists
(
select * from inserted i
join updated u
on i.pk = u.pk
and (i.mycolumn <> u.mycolumn or (i.mycolumn is null and u.mycolumn is not null) or (i.mycolumn is not null and u.mycolumn is null))
)
begin
rollback tran
raiserror ('cannot update column', 16, -1)
end
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett,
As I aware of the logical tables of trigger there 
are only two tables inserted and deleted.Updated 
table is not the logical one .Correct me If I am 
wrong ..

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources