| Author |
Topic |
|
Xerxes
Aged Yak Warrior
666 Posts |
 Posted - 2005-03-02 : 11:33:44
|
I've looked through BOL, but I haven't found an example of locking a table -- or maybe securing a table -- so that it cannot be erased, modified, or altered---but it can still be read.Usually I find the answers easily enough. Today isn't my day  .Any help is appreciated  Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-02 : 11:39:53
|
| Set the users permissions to read only on that table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-03-02 : 11:48:22
|
But isn't there a way in code to do this? Or maybe setting up the table so that a certain password is invoked before it can be deleted? I'm certain there has to be a way to do this.Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2005-03-02 : 12:00:34
|
quote:
Or maybe setting up the table so that a certain password is invoked before it can be deleted? I'm certain there has to be a way to do this.
You could write a delete trigger which rolls back the transaction to prevent all deletes, perhaps. There's no facility for 'password' access to a table in sql server other than the permissions granted at logon.JonathanGaming will never be the same |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-02 : 12:02:16
|
do all access to db with sprocs. then set persmissions on each sproc.SQL server handles security with users and roles, not direct access...Go with the flow & have fun! Else fight the flow  |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-03-02 : 12:44:48
|
| Mladen, That DOESN'T protect my table from deletion.Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-02 : 12:49:32
|
| no it doesn't. that's why all users must have roles in which you specify whichcommands they can use and which can't.and who are you afraid is going to delete the table?Go with the flow & have fun! Else fight the flow |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-02 : 19:39:56
|
| Why do users have non-read only permissions if you don't want them to? What permissions have you granted to them? Please don't say sa or db_owner. This isn't an application code thing that should fix this situation, it should be handled with permissions at the database level.Tara |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-03-02 : 20:40:41
|
quote:
Originally posted by tduggan
Why do users have non-read only permissions if you don't want them to? What permissions have you granted to them? Please don't say sa or db_owner. This isn't an application code thing that should fix this situation, it should be handled with permissions at the database level.Tara
The reason I asked this is because a table I create always seems to be empty a few days later and I am at a loss as to why.Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-02 : 21:12:15
|
| Sounds like you need to have SQL Profiler running to determine who is doing this and why. Just startup the default template. It should give you what you need to see.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-02 : 22:21:47
|
Even better, run the following for each user account in the database:DENY DELETE ON myTable TO usernameAlso remove all users from the db_owner role. Whoever is deleting your data will complain soon enough, and you can utilize your various Marine skills on them.  |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-03-02 : 23:12:42
|
quote:
Originally posted by robvolk
Even better, run the following for each user account in the database:DENY DELETE ON myTable TO usernameAlso remove all users from the db_owner role. Whoever is deleting your data will complain soon enough, and you can utilize your various Marine skills on them.
GREAT IDEA, ROB! THANKS and a SALUTE  TO YOU!Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL! |
|
|
|
|
Table Locking