How can I save my table from truncate in SQL Server
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I need to restrict my table from update and delete.
For Update and Delete I'm Using an Instead of trigger, and its working fine.
But if I use the TRUNCATE Command, it erases my data.
I need to restrict truncate or any other table modification process.
Can anyone give your valuable suggestions.
sql-server truncate edited Nov 23 '18 at 11:58
Birel
495414
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
add a comment |
I need to restrict my table from update and delete.
For Update and Delete I'm Using an Instead of trigger, and its working fine.
But if I use the TRUNCATE Command, it erases my data.
I need to restrict truncate or any other table modification process.
Can anyone give your valuable suggestions.
sql-server truncate edited Nov 23 '18 at 11:58
Birel
495414
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
Do you mean you don't want others to be able to runTRUNCATEagainst the table?
– Larnu
Nov 23 '18 at 11:04
one word: permissions.
– Mitch Wheat
Nov 23 '18 at 11:07
Yes Mr.Larnu. No one can able to perform TRUNCATE against the table
– Raju Bandaram
Nov 23 '18 at 11:11
1
Either permissions or restrictions can help you: docs.microsoft.com/en-us/sql/t-sql/statements/…
– Denis Rubashkin
Nov 23 '18 at 11:12
4
Truncate is a DDL command, and delete DML. Permissions is what you need to look into, you can explicitly deny access to just TRUNCATE if necessary, deny will always override any allow permissions.
– Apep
Nov 23 '18 at 11:15
add a comment |
I need to restrict my table from update and delete.
For Update and Delete I'm Using an Instead of trigger, and its working fine.
But if I use the TRUNCATE Command, it erases my data.
I need to restrict truncate or any other table modification process.
Can anyone give your valuable suggestions.
sql-server truncate edited Nov 23 '18 at 11:58
Birel
495414
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
I need to restrict my table from update and delete.
For Update and Delete I'm Using an Instead of trigger, and its working fine.
But if I use the TRUNCATE Command, it erases my data.
I need to restrict truncate or any other table modification process.
Can anyone give your valuable suggestions.
sql-server truncate sql-server truncate edited Nov 23 '18 at 11:58
Birel
495414
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
edited Nov 23 '18 at 11:58
Birel
495414
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
edited Nov 23 '18 at 11:58
Birel
495414
edited Nov 23 '18 at 11:58
Birel
495414
edited Nov 23 '18 at 11:58
Birel
495414
495414
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
asked Nov 23 '18 at 11:00
Raju BandaramRaju Bandaram
95
95
Do you mean you don't want others to be able to runTRUNCATEagainst the table?
– Larnu
Nov 23 '18 at 11:04
one word: permissions.
– Mitch Wheat
Nov 23 '18 at 11:07
Yes Mr.Larnu. No one can able to perform TRUNCATE against the table
– Raju Bandaram
Nov 23 '18 at 11:11
1
Either permissions or restrictions can help you: docs.microsoft.com/en-us/sql/t-sql/statements/…
– Denis Rubashkin
Nov 23 '18 at 11:12
4
Truncate is a DDL command, and delete DML. Permissions is what you need to look into, you can explicitly deny access to just TRUNCATE if necessary, deny will always override any allow permissions.
– Apep
Nov 23 '18 at 11:15
add a comment |
Do you mean you don't want others to be able to runTRUNCATEagainst the table?
– Larnu
Nov 23 '18 at 11:04
one word: permissions.
– Mitch Wheat
Nov 23 '18 at 11:07
Yes Mr.Larnu. No one can able to perform TRUNCATE against the table
– Raju Bandaram
Nov 23 '18 at 11:11
1
Either permissions or restrictions can help you: docs.microsoft.com/en-us/sql/t-sql/statements/…
– Denis Rubashkin
Nov 23 '18 at 11:12
4
Truncate is a DDL command, and delete DML. Permissions is what you need to look into, you can explicitly deny access to just TRUNCATE if necessary, deny will always override any allow permissions.
– Apep
Nov 23 '18 at 11:15
Do you mean you don't want others to be able to run
TRUNCATE against the table?– Larnu
Nov 23 '18 at 11:04
Do you mean you don't want others to be able to run
TRUNCATE against the table?– Larnu
Nov 23 '18 at 11:04
one word: permissions.
– Mitch Wheat
Nov 23 '18 at 11:07
one word: permissions.
– Mitch Wheat
Nov 23 '18 at 11:07
Yes Mr.Larnu. No one can able to perform TRUNCATE against the table
– Raju Bandaram
Nov 23 '18 at 11:11
Yes Mr.Larnu. No one can able to perform TRUNCATE against the table
– Raju Bandaram
Nov 23 '18 at 11:11
1
1
Either permissions or restrictions can help you: docs.microsoft.com/en-us/sql/t-sql/statements/…
– Denis Rubashkin
Nov 23 '18 at 11:12
Either permissions or restrictions can help you: docs.microsoft.com/en-us/sql/t-sql/statements/…
– Denis Rubashkin
Nov 23 '18 at 11:12
4
4
Truncate is a DDL command, and delete DML. Permissions is what you need to look into, you can explicitly deny access to just TRUNCATE if necessary, deny will always override any allow permissions.
– Apep
Nov 23 '18 at 11:15
Truncate is a DDL command, and delete DML. Permissions is what you need to look into, you can explicitly deny access to just TRUNCATE if necessary, deny will always override any allow permissions.
– Apep
Nov 23 '18 at 11:15
add a comment |
1 Answer
1
active
oldest
votes
To run a TRUNCATE statement you need to have the ALTER permissions on the object; as per the documentation: TRUNCATE TABLE (Transact-SQL) - Permissions.
If you want to stop a user/role from using the TRUNCATE statement on a table, you will need to use the following (Replacing the text in braces ({})):
USE {YourDatabase}
DENY ALTER ON {YourTable} TO {User/Role};
You can easily test if this works with a quick test script:
USE Sandbox;
GO
CREATE TABLE dbo.TestTable (Id int IDENTITY(1,1), String varchar(10));
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
GO
CREATE USER TestUser WITHOUT LOGIN;
ALTER ROLE db_ddladmin ADD MEMBER TestUser;
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This works
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
DENY ALTER ON dbo.TestTable TO TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This fails
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
GO
--Clean up
DROP TABLE dbo.TestTable;
DROP USER TestUser;
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53445430%2fhow-can-i-save-my-table-from-truncate-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To run a TRUNCATE statement you need to have the ALTER permissions on the object; as per the documentation: TRUNCATE TABLE (Transact-SQL) - Permissions.
If you want to stop a user/role from using the TRUNCATE statement on a table, you will need to use the following (Replacing the text in braces ({})):
USE {YourDatabase}
DENY ALTER ON {YourTable} TO {User/Role};
You can easily test if this works with a quick test script:
USE Sandbox;
GO
CREATE TABLE dbo.TestTable (Id int IDENTITY(1,1), String varchar(10));
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
GO
CREATE USER TestUser WITHOUT LOGIN;
ALTER ROLE db_ddladmin ADD MEMBER TestUser;
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This works
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
DENY ALTER ON dbo.TestTable TO TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This fails
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
GO
--Clean up
DROP TABLE dbo.TestTable;
DROP USER TestUser;
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
add a comment |
To run a TRUNCATE statement you need to have the ALTER permissions on the object; as per the documentation: TRUNCATE TABLE (Transact-SQL) - Permissions.
If you want to stop a user/role from using the TRUNCATE statement on a table, you will need to use the following (Replacing the text in braces ({})):
USE {YourDatabase}
DENY ALTER ON {YourTable} TO {User/Role};
You can easily test if this works with a quick test script:
USE Sandbox;
GO
CREATE TABLE dbo.TestTable (Id int IDENTITY(1,1), String varchar(10));
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
GO
CREATE USER TestUser WITHOUT LOGIN;
ALTER ROLE db_ddladmin ADD MEMBER TestUser;
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This works
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
DENY ALTER ON dbo.TestTable TO TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This fails
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
GO
--Clean up
DROP TABLE dbo.TestTable;
DROP USER TestUser;
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
add a comment |
To run a TRUNCATE statement you need to have the ALTER permissions on the object; as per the documentation: TRUNCATE TABLE (Transact-SQL) - Permissions.
If you want to stop a user/role from using the TRUNCATE statement on a table, you will need to use the following (Replacing the text in braces ({})):
USE {YourDatabase}
DENY ALTER ON {YourTable} TO {User/Role};
You can easily test if this works with a quick test script:
USE Sandbox;
GO
CREATE TABLE dbo.TestTable (Id int IDENTITY(1,1), String varchar(10));
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
GO
CREATE USER TestUser WITHOUT LOGIN;
ALTER ROLE db_ddladmin ADD MEMBER TestUser;
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This works
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
DENY ALTER ON dbo.TestTable TO TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This fails
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
GO
--Clean up
DROP TABLE dbo.TestTable;
DROP USER TestUser;
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
To run a TRUNCATE statement you need to have the ALTER permissions on the object; as per the documentation: TRUNCATE TABLE (Transact-SQL) - Permissions.
If you want to stop a user/role from using the TRUNCATE statement on a table, you will need to use the following (Replacing the text in braces ({})):
USE {YourDatabase}
DENY ALTER ON {YourTable} TO {User/Role};
You can easily test if this works with a quick test script:
USE Sandbox;
GO
CREATE TABLE dbo.TestTable (Id int IDENTITY(1,1), String varchar(10));
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
GO
CREATE USER TestUser WITHOUT LOGIN;
ALTER ROLE db_ddladmin ADD MEMBER TestUser;
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This works
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
DENY ALTER ON dbo.TestTable TO TestUser;
GO
EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This fails
GO
REVERT;
GO
SELECT *
FROM dbo.TestTable;
GO
--Clean up
DROP TABLE dbo.TestTable;
DROP USER TestUser;
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
edited Nov 23 '18 at 11:35
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
answered Nov 23 '18 at 11:28
LarnuLarnu
22.5k51933
22.5k51933
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53445430%2fhow-can-i-save-my-table-from-truncate-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you mean you don't want others to be able to run
TRUNCATEagainst the table?– Larnu
Nov 23 '18 at 11:04
one word: permissions.
– Mitch Wheat
Nov 23 '18 at 11:07
Yes Mr.Larnu. No one can able to perform TRUNCATE against the table
– Raju Bandaram
Nov 23 '18 at 11:11
1
Either permissions or restrictions can help you: docs.microsoft.com/en-us/sql/t-sql/statements/…
– Denis Rubashkin
Nov 23 '18 at 11:12
4
Truncate is a DDL command, and delete DML. Permissions is what you need to look into, you can explicitly deny access to just TRUNCATE if necessary, deny will always override any allow permissions.
– Apep
Nov 23 '18 at 11:15