May 20, 2010 at 8:09 pm
Hi,
I am a beginner to sql server..so please help me on my question...
I have used sql server userID and password in ssis and i was able to delete the table in the production according to our requirement and now i want to drop the constraints and truncate the table load the data and re create the constraints by using the same sql server user id and password in the production database...
My Question is .. can I use the same user id and password to truncate the table...which i used to delete a diff table in the production before...
Does truncate table needs additional permissions to a user who already has delete permissions on the tables......
May 21, 2010 at 12:54 am
TRUNCATE TABLE permissions is default to the members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. I think you should be able to do.. but if you can tel us the permissions that your user is having then it is easy to say..
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 21, 2010 at 1:08 am
TRUNCATE requires ALTER permission on the table which you will also need for adding the constraint. You will probably also require ALTER on the schema too!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 21, 2010 at 5:09 am
In SQL 2000, you cannot give anyone else permission to truncate your table.
I have always thought this was a bit strange, since truncate is so much
faster. You can grant someone permission to delete, which means they can
delete all the rows very slowly, but you can't give them truncate permission
,which would be a lot more efficient.
As Tibor said, there are ways around this in SQL 2005.
May 21, 2010 at 7:39 am
saimonkhan15 (5/21/2010)
In SQL 2000, you cannot give anyone else permission to truncate your table.I have always thought this was a bit strange, since truncate is so much
faster. You can grant someone permission to delete, which means they can
delete all the rows very slowly, but you can't give them truncate permission
,which would be a lot more efficient.
As Tibor said, there are ways around this in SQL 2005.
This pots relates to SQL Server 2008, BTW who is Tibor??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 21, 2010 at 9:48 am
User has only db_owner permissions ...no permissions are given on the Server Role...
May 21, 2010 at 10:53 am
Nagesh S-432384 (5/21/2010)
TRUNCATE TABLE permissions is default to the members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. I think you should be able to do.. but if you can tel us the permissions that your user is having then it is easy to say..Nag
giving someone rights to the db_owner role would give them the ability to truncate a table...as a mater of fact, they could drop the database with the db_owner role;
if security is an issue, you should probably change that...
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply