Murray Brown
asked on
Vb.net/SQL prevent a table from being deleted ever
Hi
I have a SQL table that has years of information that I don't anyone using my VB.net application (that allows them to run SQL script) to delete. Is there a way of locking this table somehow to prevent deletion?
Thanks
I have a SQL table that has years of information that I don't anyone using my VB.net application (that allows them to run SQL script) to delete. Is there a way of locking this table somehow to prevent deletion?
Thanks
ASKER
Here is the VB.net code I use to delete the table
sSQL = "DROP TABLE [" & oSelectedTable & "]"
Dim cn As New SqlConnection(oConnectionS tring)
Dim cmd As New SqlCommand(sSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
sSQL = "DROP TABLE [" & oSelectedTable & "]"
Dim cn As New SqlConnection(oConnectionS
Dim cmd As New SqlCommand(sSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have to admit, that I don't understand the problem. When you don't want to drop the table, why do you offer a possibility to do so in your application?
Use a whitelist of tables which the users are allowed to drop with your application.
The other approach would be a DDL trigger do prevent (intercept) the DROP of a certain table. Something like
Use a whitelist of tables which the users are allowed to drop with your application.
The other approach would be a DDL trigger do prevent (intercept) the DROP of a certain table. Something like
CREATE TRIGGER tr_Sample ON DATABASE
FOR DROP_TABLE
AS
DECLARE @EventData XML = EVENTDATA();
DECLARE @ObjectName SYSNAME = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');
IF @ObjectName = 'yourTableName'
BEGIN
RAISERROR ('Don''t try that..', 16, 1);
ROLLBACK;
END;
Here we would like to know what you are getting as a output when you are trying to delete the table from your VB.NET. code.
ASKER
thanks
Can you post your query you are using to delete it? Also the error ?