grant truncate table permission - how?

User1 has permission to run an sp (on sqlServer2k). The
sp truncates a table and then does other stuff. I get the
permission denied message for user1 to perform this
operation. I tried
Grant Truncate Table to User1
in query analyzer, but get the message
Privilege TRUNCATE TABLE may not be granted or revoked.
How do I give user1 permission to trucate tables in DB1?
Thanks,
RonHi Ron,
A user with db_ddladmin role can issue any DDL command inside the database
he is assigned the role. We can not revoke the permission for a specific
object
from the user if he is the part of db_ddladmin role.
Can he only truncate the tables he is allowed access to in the particular
role he is a member of for a respective database?
He can Truncate the tables only on that particular database for which he got
db_ddladmin role.
Thanks
Hari
SQL Server MVP
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:201301c53edc$af1603f0$a401280a@.phx.gbl...[vbcol=seagreen]
>I added user1 to ddladmin. This seems to work. Does this
> mean user1 can truncate any table he wants? Is it
> possible to restrict what tables user1 can truncate? Or
> can he only truncate the tables he is allowed access to in
> the particular role he is a member of for a respective
> database?
> Thanks,
> Ron
>
> the|||Thanks for your reply. I see where I made a mistake. I
placed user1 in ddladmin on the security foler in EM. I
saw the ddladmin in the respective database. Now I have
to change that permission. Well, it is all a learning
process. Thanks again.

>--Original Message--
>Hi Ron,
>A user with db_ddladmin role can issue any DDL command
inside the database
>he is assigned the role. We can not revoke the
permission for a specific
>object
>from the user if he is the part of db_ddladmin role.
>Can he only truncate the tables he is allowed access to
in the particular
>role he is a member of for a respective database?
>He can Truncate the tables only on that particular
database for which he got
>db_ddladmin role.
>Thanks
>Hari
>SQL Server MVP
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:201301c53edc$af1603f0$a401280a@.phx.gbl...
this[vbcol=seagreen]
Or[vbcol=seagreen]
to in[vbcol=seagreen]
The[vbcol=seagreen]
DB1?[vbcol=seagreen]
>
>.
>

Grant rights to Truncate table to specific user 2000/2005

Hi
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>

Grant Premission

How do I grant permission to another user in a database to truncate a table which is owned by me.
By default only the owner of the table, DBO, SYSADMIN can truncate any table.
Is there any other alternat way that I can look for doing it ?
ThanksLike you've stated, the permiisions on TRUNCATE are;

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

Two alternatives:
[list=1]
GRANT DELETE permission on the table to the user and then issue DELETE statement
Create a stored procedure that does the TRUNCATE and then GRANT EXECUTE on the stored procedure to the user
[/list=1]|||Thanks man.
The second option was preferable.

Actually I tried to create a procs using the users id and while he had the ddl_admin role granted, then till he had that role he was able to truncate it, but once i revoked the role from that user, he was not able to truncate.

Thanks for your help man.|||Some how after granting the execute permission to the proc, the user is still not able to tryncate the table.

It still gives the permission denied error.

Thanks

Grant permission to table for truncate table

hi,
we have object-level user security. That user need permission for truncate
one table. I can't set the user as sysadmin or DBO.
any way that I could grant the user to have truncate that table?
Thanks
If you are on 2005, you can grant CONTROL permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>

Grant permission to table for truncate table

hi,
we have object-level user security. That user need permission for truncate
one table. I can't set the user as sysadmin or DBO.
any way that I could grant the user to have truncate that table?
ThanksDELETE ?
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>|||For some reason we could NOT use delete.
1. Identity id reset.
2. large table
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> DELETE ?
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
>|||"EXECUTE AS" ?
http://msdn2.microsoft.com/en-us/library/ms188354.aspx
"mecn" wrote:

> For some reason we could NOT use delete.
> 1. Identity id reset.
> 2. large table
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
>
>|||On Aug 2, 8:52 am, Pranil <Pra...@.discussions.microsoft.com> wrote:
> "EXECUTE AS" ?
> http://msdn2.microsoft.com/en-us/library/ms188354.aspx
>
> "mecn" wrote:
>
>
>
> - Show quoted text -
You need to have alter permission on the table to get the truncate
permission.
Thanks
Shiju Samuel|||If you are on 2005, you can grant CONTROL permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...

> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>

Grant permission to table for truncate table

hi,
we have object-level user security. That user need permission for truncate
one table. I can't set the user as sysadmin or DBO.
any way that I could grant the user to have truncate that table?
ThanksDELETE ?
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>|||For some reason we could NOT use delete.
1. Identity id reset.
2. large table
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> DELETE ?
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> we have object-level user security. That user need permission for
>> truncate one table. I can't set the user as sysadmin or DBO.
>> any way that I could grant the user to have truncate that table?
>> Thanks
>|||"EXECUTE AS" ?
http://msdn2.microsoft.com/en-us/library/ms188354.aspx
"mecn" wrote:
> For some reason we could NOT use delete.
> 1. Identity id reset.
> 2. large table
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> > DELETE ?
> >
> >
> >
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> >> hi,
> >> we have object-level user security. That user need permission for
> >> truncate one table. I can't set the user as sysadmin or DBO.
> >> any way that I could grant the user to have truncate that table?
> >>
> >> Thanks
> >>
> >
> >
>
>|||On Aug 2, 8:52 am, Pranil <Pra...@.discussions.microsoft.com> wrote:
> "EXECUTE AS" ?
> http://msdn2.microsoft.com/en-us/library/ms188354.aspx
>
> "mecn" wrote:
> > For some reason we could NOT use delete.
> > 1. Identity id reset.
> > 2. large table
> > "Uri Dimant" <u...@.iscar.co.il> wrote in message
> >news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> > > DELETE ?
> > > "mecn" <mecn2...@.yahoo.com> wrote in message
> > >news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> > >> hi,
> > >> we have object-level user security. That user need permission for
> > >> truncate one table. I can't set the user as sysadmin or DBO.
> > >> any way that I could grant the user to have truncate that table?
> > >> Thanks- Hide quoted text -
> - Show quoted text -
You need to have alter permission on the table to get the truncate
permission.
Thanks
Shiju Samuel|||If you are on 2005, you can grant CONTROL permissions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>sql