We are migrating from a SQL Server 2005 environment where there were certain IDs that had Sysadmin privileges to a new SQL Server 2008 environment and I have taken away the Sysadmin privileges but I am running into snags with permissions.
I have several SQL logins that need to be able to truncate tables (within an SSIS package) but I cannot get the permissions right. I have had some success granting access like this:
GRANT ALTER ON OBJECT::schema.tablename TO username;
but it seems tedious to do this for any object where truncate or alter privilege is needed. How do I Grant a user the proper privilege to be able to truncate tables within a Database?