restrict deletion

What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?

Thank you in advance.

I have used same practice.|||Thank you Javier.|||

Hi fpena,

You can just compare the username again to the current user to see if he is the owner of this record. If yes, delete it. All this can be done in a single stored procedure.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||could you point me on the right direction? Thank you.|||

Hi fpena,

You can use a single sentence to achieve that. Assume that there is a field in the table named RecordOwner, and your stored procedure passes in a parameter named @.Deleter. You can use

DELETE FROM Table1 WHERERowID=@.RowID ANDRecordOwner=@.Deleter

You can check the AffectedRows in your code. If it is 0, it means that the row does not exist or the deleter is not the owner, so that he cannot delete it.

|||i'm not sure i can accomplish what i'm looking for with that. i have a gridview visible to everyone all records visible no matter what userID owns the record what i'm trying to do is prevent a user who doesn't own a record from deleting when trying to delete. hope my question is clear enough. Thank you.|||

Hi fpena,

I'm sure that this will do what you need. You just need to modify the delete command you're currently using to check the ownership info.

If you have any questions on how to do this, please feel free to reply to my post.

|||

how is it done?

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...
Thanks in advance...
Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...
|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...
|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:

> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>
>
|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...[vbcol=seagreen]
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...
Thanks in advance...Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:
> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> > Hi,
> >
> > I have unknowingly deleted 100 records instead of 1 record.
> > I haven't set autocommit off also...
> > Could you please let me know if there is any way to get back those
> > records...
> >
> > Thanks in advance...
>
>|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
>> Did you take a backup prior to doing the delete?
>> Last night or something?
>> You could restore it with a different DB name and import the records...
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "Srik" <Srik@.discussions.microsoft.com> wrote in message
>> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>> > Hi,
>> >
>> > I have unknowingly deleted 100 records instead of 1 record.
>> > I haven't set autocommit off also...
>> > Could you please let me know if there is any way to get back those
>> > records...
>> >
>> > Thanks in advance...
>>|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records..
.
Thanks in advance...Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:

> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>
>|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...[vbcol=seagreen]
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
>|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...