setting an attribute AS uneditable in sql server

Question

5.00/5 (1 vote)

See more:

I have a table like this

SQL

create table cus(
username varchar(20) primary key,
password varchar(50)
);

My business requirement is such that i should not be able to edit the username field. What i mean is i need to rollback a transaction if there is an update statement.
What i do is i create a trigger something like this

SQL

create trigger cusonupdate on dbo.cus
for update 
as
rollback
print'The suggested Row cannot be updated'

This will do the required thing. But what i need is i must be having a provision to update the password. How should i do it?

Posted 14-Oct-13 21:09pm

Rakshith Kumar

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Madhu Nair · Accepted Answer · 2013-10-14T21:20:00

Solution 1

Try this way -

SQL

create trigger cusonupdate on dbo.cus
for update
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF UPDATE(username)
        BEGIN
            ROLLBACK
            print'The suggested Row cannot be updated'
        END

END

Posted 14-Oct-13 21:20pm

Madhu Nair

Comments

Rakshith Kumar 15-Oct-13 3:29am

Its working :). Thanks a ton

Tejas Vaishnav · Accepted Answer · 2013-10-14T21:26:00

Solution 2

Hello freind you can use DENY on update with your cus table, something like this...

SQL

DENY UPDATE ON dbo.cus (username ) TO <database username>

with DENY keywork you need to specify sql server user for which you need to DENY update

Referance :
DENY[^]

Posted 14-Oct-13 21:26pm

Tejas Vaishnav

Updated 14-Oct-13 21:27pm

v2

Comments

Rakshith Kumar 15-Oct-13 3:32am

Nice one tejas :).

Tejas Vaishnav 15-Oct-13 4:31am

Thanks

Madhu Nair 15-Oct-13 4:34am

Very Good!!!!

Tejas Vaishnav 15-Oct-13 4:38am

Thanks

Rakshith Kumar 15-Oct-13 5:17am

My dear i guess its only for the sql server users ? Correct me if iam wrong. The thing is i have a table customer and i need to set the username as un editable. denying the update previlege will mean that you are denying the sql server user the previlege to edit.

Rakshith Kumar 15-Oct-13 5:02am

@Tejas Vaishnav: My dear i guess its only for the sql server users ? Correct me if iam wrong. The thing is i have a table customer and i need to set the username as un editable. denying the update previlege will mean that you are denying the sql server user the previlege to edit.

Tejas Vaishnav 15-Oct-13 7:12am

yes you are right, its an example for sql server, and it will deny sql server user to update that specific column for specific table.

Rakshith Kumar 15-Oct-13 7:23am

If that is the case it will only deny the permission to the users of the database. My question however was to deny the users present in the table to deny the update permission. It will not work for my requirement. But thank you for the letting me know the use of granting and denying the permission. Thanks again :)

Tejas Vaishnav 15-Oct-13 8:46am

Let me explain, in your website/web application you need a functionality like that, the user of website is not able to change their username? am i right? the only thing is update able which is password from cus table.

that's what the thing is working, because in your website you are connecting to database using any username or password in your database connection string, just deny that user for updating the cus->username. so while user is trying to update it, sql server is deny update because that user has no rights to update the username.

if you are going with trigger then there is no any sqlserver user will able to update that column, and in deny case if you are not able to update the column value using xyz then admin can log in using super user or sa and can update that column, so in this case you have one option left if any how you need to update that column

Rakshith Kumar 15-Oct-13 23:26pm

Thank you a lot tejas