"TRUNCATE TABLE" in a sproc w/locked-down user

Hullo folks, I'm having what I assume is a fairly mundane security issue.

I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.

Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.

Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?The only people who can use 'truncate' are the table owner, sysadmin, db_owner, and db_ddladmin; so if you have a user whose permissions are severely restricted I would not think 'truncate' would work, or if it would it would be a phaff to set up.

Why not just DELETE FROM, or use a temp table to create the statistics and return them?|||This is effectively what I ended up doing after making the same permissions determination. I had my heart set on Truncate do to the nature of the process itself, but I was able to work around the restriction using DELETE FROM and a local scheduled maintenance job.|||Is there a way he can call an osql command via xp_cmdshell that connects with a trusted connection that can truncate the table ? I tried some stuff with a server link to itself, but cannot truncate table link.db.owner.table, same goes with openquery (truncate not supported).|||That would present a larger security issue then outright granting the user required permissions. I definately don't want to expose trusted command line functionality to this login.

What I have now works, it's just not exactly the way I want it to. All of my research indicates that it will just have to be good enough as I cannot get around that pesky permissions requirement for TRUNCATE.|||In the past I have allowed a user to own a single table for a similar purpose. But that was for an import table, where the data was truncated, reloaded, diced up, and distributed to other tables. No other process even knew the table was there. It gets you out of having to have db_owner rights, but there is no other way around the requirement of owning the table.|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

have an admin account run the nightly process, but the restricted user still gets their statistics

just a thought|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

have an admin account run the nightly process, but the restricted user still gets their statistics

just a thought
That's the current working solution. The process runs a couple times a day, but same idea.

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tws I can make right now will be of benefit.
Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tws I can make right now will be of benefit.
> Thanks
> Steve ...