March 18, 2015 at 9:57 am
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?
March 18, 2015 at 10:10 am
No good news, I'm afraid
https://msdn.microsoft.com/en-us/library/ms177570.aspx
Permissions
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2015 at 10:56 am
granting execute at schema level is the only other way but it OTP in this case by the sound of it.
The object grants you are currently performing are the only controlled way, why is that so much of an issue?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 18, 2015 at 11:04 am
What I am running into now is this error when attempting to grant Alter when the user is the schema owner:
GRANT ALTER ON OBJECT::schema.tablename TO username;
GO
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
March 18, 2015 at 11:30 am
can you provide more info on how this user maps into the database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply