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 Showplan to <user>

I was trying to review some query statistics and received the following message:

SHOWPLAN permission denied in database Test

I gave the user permission by the following command:

Grant showplan to user.

I am curious as to how much perfomance does this effect? Is there an alternative?

regards

The SHOWPLAN permission only governs who can run the various SET SHOWPLAN statements. It doesn't impact performance of the server per se. And with some of the SHOWPLAN statement in effect, the statement(s) is not executed and goes through compilation phase only. There is no other alternative though other than granting the required permission to the user.|||

Does it have anything to do with "Display estimated execution plan" or "Include actual execution plan" in query analyzer?

Thanks

|||

Yes. Those features run one of the SET SHOWPLAN command underneath the covers. See the BOL link below for more details on the permissions:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9c18657e-2992-4ee7-ab07-7af3141ec658.htm

The online version of BOL links is:

http://msdn2.microsoft.com/en-us/ms189602(SQL.90).aspx

|||

Thanks for your prompt reply.

Just one last question.

If i have a query that takes about 60 minutes to run and i have the "Show actual estimation plan" selected...that would not effect the server perrformance or clog it up as compare to if i just run the query?

Thanks

|||The actual estimation plan uses "SET STATISTICS XML ON" in SQL Server 2005 and "SET STATISTICS PROFILE ON" in older versions of SQL Server. It will not affect the query performance drastically. You may want to just use SET STATISTICS TIME ON first to see the compilation and execution times. That will give an idea as to where the chunk of query execution time is being spent. You can then use the actual plan output to look for missing indexes, scans etc.

GRANT SELECT on View

I have granted SELECT permissions on a view to a user, now when I run the
view it tells me that the user is denied select permissions on a table. The
user does not have any other permissions set on any tables or views other
than the SELECT on this particular view. How can I allow the user to view
this data?
Thank you in advance,
RuiThis sounds like a broken ownership problem. Does the same user (e.g. dbo)
own the view as owns the table? To avoid this problem, both objects will
need to be owned by the same user.
"Rui Peres" <peresr@.southcoast.org> wrote in message
news:u2quzdN0FHA.2960@.tk2msftngp13.phx.gbl...
>I have granted SELECT permissions on a view to a user, now when I run the
> view it tells me that the user is denied select permissions on a table.
> The
> user does not have any other permissions set on any tables or views other
> than the SELECT on this particular view. How can I allow the user to view
> this data?
> Thank you in advance,
> Rui
>|||Thanks, that did it.
Rui
"Lori Clark" <lclark@.dbadvisor.com> wrote in message
news:ebAkZvN0FHA.460@.TK2MSFTNGP15.phx.gbl...
> This sounds like a broken ownership problem. Does the same user (e.g.
dbo)
> own the view as owns the table? To avoid this problem, both objects will
> need to be owned by the same user.
>
> "Rui Peres" <peresr@.southcoast.org> wrote in message
> news:u2quzdN0FHA.2960@.tk2msftngp13.phx.gbl...
other[vbcol=seagreen]
view[vbcol=seagreen]
>