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.
| Author |
Topic |
|
Kyle.l.watson@gmail.com
Starting Member
2 Posts |
 Posted - 2007-08-29 : 15:22:51
|
| Hi,Let us say we have a table in our database called appointments. Now, all users to our database have permissions to insert into this table. However, I want to set the permission to allow updates only on the records that that particular user inserted and also delete.So basically, a user can only delete/update a record that he or she created. How do you do this? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-08-29 : 15:40:12
|
Add a column which will contain user's login/app defined username to the table. Then while updating the records:Update TableSet col1 = something...Where UserName = @UserName Where, @UserName will contain user's login name.Similar approach for delete as well.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Kyle.l.watson@gmail.com
Starting Member
2 Posts |
Posted - 2007-08-29 : 15:57:57
|
quote:
Originally posted by harsh_athalye
Add a column which will contain user's login/app defined username to the table. Then while updating the records:Update TableSet col1 = something...Where UserName = @UserName Where, @UserName will contain user's login name.Similar approach for delete as well.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
Thanks for the reply. This update statement would be applicable at the application level. Is it possible/advisable to enforce this at the database level?So, perhaps a trigger that says if on update current UserID does not equal UserName then roll back transaction. Obviously you want to avoid using triggers to roll back since it is expensive to do so. |
|
|
|
|
|
|
Authentication and permissions