"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.

'-' in database name

Hi All,
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> something
> command:
>

'-' in database name

Hi All,
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > Hi All,
> >
> > We have a couple development database that contain a dash in them,
> something
> > like 'DB-TEST'.
> >
> > I need to truncate the log and I would like to issue the following
> command:
> >
> > backup log 'DB-TEST' with truncate_only
> >
> > but sql-server doesn't like it
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> > database names.
> >
> > This fails both interactively and in a job.
> >
> > Any help in escaping the - would be greatly appreciated.
> >
> > TIA,
> > Tim
> >
> >
>