December 23, 2002 at 7:31 am
How a user can be granted TRUNCATE TABLE permission on A Table OR all Tables in a DB in SQL Server if user is not Table Owner and DB Owner ???
December 23, 2002 at 11:25 am
This is not possible. It would be too dangerous due to lack of full logging of such an operation.
December 23, 2002 at 12:54 pm
Couldn't this be accomplised by use of a sproc?
Everett Wilson
ewilson10@yahoo.com
December 23, 2002 at 1:20 pm
No, it could not. It is a special one. But try it.
December 23, 2002 at 2:12 pm
Thanks for the lesson, I couldn't make it work.
Everett Wilson
ewilson10@yahoo.com
December 23, 2002 at 4:31 pm
In SQL2K you can make them a member of the DDL_Admin role. Not much better, but a little. Truncates are logged, just at the page level - in other words, you can roll it back. Remember also that truncate will reset the seed if you have an identity col.
If you need to do this, making the user the table owner is not a bad way to go. If you had one table and potentially multiple users (or even multiple instances of the same application), one user could truncate data being used by another. If each person has their own table, much less risk and much less work to prevent problems. Im not generally in favor of anyone other than dbo owning objects, but if you have a need for truncate, this is a good place to use it.
Finally, if you need to do a truncate without making them the owner or putting them into a role with more permissions than you feel appropriate, there are two work arounds. The first is to open a connection inside your app that users a different login with the appropriate permissions, that way your app controls the execution and the user never knows the login/password for the truncate. The other way is to push a row into a table, then have a job that runs, looks for the row (a flag basically), then does the delete.
Andy
December 24, 2002 at 12:22 am
Great response!! thanks Andy.
In fact we have few tables that are being used as temporary tables within the production DB instead of tempdb as scope of the these tables are not limited to one procedure rather multiple procedures access their data once processed. But these tables need delete for next data processing. Delete takes much longer time to process whereas application doesn;t wants to log its delete operation.
Since we are using one user to access all tables, I think making user table owner for these temp tables is better choice.
December 26, 2002 at 12:39 pm
Remember, when using Temp tables, sometimes it is better to use a master calling procedure that creates the temp table. FOr example, if you are using a Global Temp table so you can call proc A, B, and C... instead you can use proc Z that creates a local temp table, then calls A, B, and C. This way, you reduce the concurency issues, and you can still use the TempDB and not have issues related to having to truncate (since you can trucate a #temp table, because by definition, you are the owner). We'd have to know a good bit more to give a more specific answer. Just remember #temp tables will exist in all child stored procedures.
December 26, 2002 at 11:05 pm
Making table owner other than DBO works when we execute procedure thru query analyser and within crystal reports but somehow it does'nt work when we call crystal report viewer within the application.
Application get stuck at TRUNCATE TABLE command. When we change it to DELETE command it works. We are still unable to pin point the reason. Do you guys have any idea??
December 26, 2002 at 11:37 pm
It is probably running under a different user account from CR Viewer. Find out what that account is and make it a table owner. Or make a user role an owner and add as many users as you need to the role.
December 27, 2002 at 7:47 am
There is only one user other than DBO. All objects are owned by DBO except temp tables under discussion which are NOW owned by this user say ABC.
Application uses ABC user to connect to DB using ABC_role which has been assigned SELECT/UPDATE/DELETE permissions on all tables that are owned by DBO and EXEC on all SP's owned by DBO.
Since ABC user is owner of temp tables, this user has TRUNCATE TABLE as default privilege. What is causing CR viewer not to execute at this point.??
December 31, 2002 at 8:09 am
Expanding on the SQL job concept, rather than changing the table owner to a user other than dbo. You could give the user permission to execute only one job which truncates tables. First create a job which truncates the tables, name the job something like "MyAppNameTruncTables". Ensure the job is owned by a user with truncate table permissions, then add a role "MyAppNameJobExec" to msdb and add the users to the role , finally alter the system view msdb..sysjobs_view and add the following lines to the end of view:
OR ((ISNULL(IS_MEMBER(N'MyAppNameJobExec'), 0) = 1)
AND name = 'MyAppNameTruncTables')
Any user in the msdb/MyAppNameJobExec role will have permissions to execute only the job MyAppNameTruncTables and no other job. In your application you can then call sp_start_job @job_name = 'MyAppNameJobExec'
December 31, 2002 at 8:15 am
Interesting idea. Im not opposed to modifying system objects when it makes sense, not sure in this case it's worth it. The headache with this one is that a service pack or RebuildM may wipe out the change, so you have to remember two years after you made the change that you did it.
Andy
January 13, 2003 at 2:47 pm
you cant give truncate table rights but you can delete from the table and then reset the identity column in a sp. Which will accomplish the same thing, except for the number of records can make it an issue.
January 13, 2003 at 4:20 pm
True, a delete and a DBCC CHECKIDENT (@table_name, RESEED, @id) will do the same thing, but it could potentially take a great deal more time (due to the logging nature of the Delete process). Performance is always an issue, so if you have the ability to use Temp tables, that would be your simplist solution.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply