MS SQL Server - truncate table

Author

truncate table

DeeJay Puar

2006-01-26, 4:53 pm

I need little clarification on truncating tables. Here is what BOL sys:

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.

Just to make sure I undertand it correctly:

Can I grant a user ddl_admin rights to truncate tables, even if this user id
does not own the table? For Example, I have a table owned by 'dbo', if I
grant 'joe' (not a dbo or object owner) ddl_admin, can he truncate the table?

Hope this is clear.

TIA,

DeeJay

Kalen Delaney

2006-01-26, 4:53 pm

Hi DeeJay

The terminology you are using is a little unusual. When we talk about
granting rights, it usually means with the GRANT command. And although you
can GRANT someone the right to delete from your table, you cannot GRANT them
the right to TRUNCATE. Transferable means with the GRANT command.

However, if you are in an appropriate role, you can assign a user to a role
that allows them to truncate the table.

So if you assign joe to the ddl_admin role, he can truncate the table. But
YOU have to be in a role that allows you to assign joe to this role.

There is no such thing as 'ddl_admin right to truncate tables'. If you
assign joe to the ddl_admin role, he will be able to truncate any table in
the database. It's part of what it means to be in the ddl_admin role.
..
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com

"DeeJay Puar" < DeeJayPuar@discussio
ns.microsoft.com> wrote in message
news:BFB6552F-FE4F-4F9E-A5EB- 74FF8E15A956@microso
ft.com...
>I need little clarification on truncating tables. Here is what BOL sys:
>
> 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.
>
> Just to make sure I undertand it correctly:
>
> Can I grant a user ddl_admin rights to truncate tables, even if this user
> id
> does not own the table? For Example, I have a table owned by 'dbo', if I
> grant 'joe' (not a dbo or object owner) ddl_admin, can he truncate the
> table?
>
> Hope this is clear.
>
> TIA,
>
> DeeJay
>
>
>

DeeJay Puar

2006-01-26, 4:53 pm

Thanks Kalen,

I was rushing and did not use the terminology correctly; it was not my intent.

That was my understanding and just wanted to confirm it.

Also, I like your SQL Server 2000 Book, when is the 2005 coming out?

Thanks,

DeeJay

"Kalen Delaney" wrote:

>
> Hi DeeJay
>
> The terminology you are using is a little unusual. When we talk about
> granting rights, it usually means with the GRANT command. And although you
> can GRANT someone the right to delete from your table, you cannot GRANT them
> the right to TRUNCATE. Transferable means with the GRANT command.
>
> However, if you are in an appropriate role, you can assign a user to a role
> that allows them to truncate the table.
>
> So if you assign joe to the ddl_admin role, he can truncate the table. But
> YOU have to be in a role that allows you to assign joe to this role.
>
> There is no such thing as 'ddl_admin right to truncate tables'. If you
> assign joe to the ddl_admin role, he will be able to truncate any table in
> the database. It's part of what it means to be in the ddl_admin role.
> ..
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www. solidqualitylearning
.com
>
>
> "DeeJay Puar" < DeeJayPuar@discussio
ns.microsoft.com> wrote in message
> news:BFB6552F-FE4F-4F9E-A5EB- 74FF8E15A956@microso
ft.com...
>
>
>
>

Sponsored Links