Permissions problem

Permissions problem

Post by venka » Fri, 19 Jul 2002 22:04:06



Hi,

I have procedure with in the procedure I  there is a
Truncate table table name. According to sql server books
on line. If i give execute permission to this procedure to
a user that user should be able to execute this procedure
with out any other permissions on the table. But that’s
not true, it gives error does not have a truncate table
permissions on the table. I know it works fine if I
give "db_ddladmin" permissions on that user.

But I cannot give this permission on the production
database to a normal user.

If anybody has the solution please let me know.

Thanks a millions
Venkat

610 834 5241

 
 
 

Permissions problem

Post by Andrés Taylo » Fri, 19 Jul 2002 22:14:42


Venkat,

TRUNCATE TABLE permissions are not tied to the table. So it's not treated
like SELECT, UPDATE, DELETE and INSERT permissions. From BOL:

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.

HTH,

--
Andrs Taylor


http://www.sql.nu/


> Hi,

> I have procedure with in the procedure I  there is a
> Truncate table table name. According to sql server books
> on line. If i give execute permission to this procedure to
> a user that user should be able to execute this procedure
> with out any other permissions on the table. But that’s
> not true, it gives error does not have a truncate table
> permissions on the table. I know it works fine if I
> give "db_ddladmin" permissions on that user.

> But I cannot give this permission on the production
> database to a normal user.

> If anybody has the solution please let me know.

> Thanks a millions
> Venkat

> 610 834 5241


 
 
 

1. Permissions problem with INFORMATION_SCHEMA views

Having security problems with the Information_Schema views in SQL 7 (sp2)

problem boils down to:

users (even when in dbo role for this db) can't see anything in the
information_schemas.key_column_usage (virtual) view
though they can see the contents of the information_schema.columns view.
(Note that though they can't see the contents, this is not a permissions
issue on the view itself - all users can (by default) SELECT from the
information_schema views)

Server administrators can see both fine (and yes, there _are_ some PK-FK
constraints to see)

So there's a SQL permissions problem somewhere along the line that being SA
bypasses, but isn't (?!) got round by being DBO (though that's not a
solution anyhow).

For a practical example I created a database with just two tables in, one of
which has a FK relationshipt to the PK in the other. My test user gets
different results from
    select top 1 * from information_schema.key_column_usage    ->doesn't get
anything
    select top 1 * from information_schema.columns    ->gets same as me

Anyone got any bright ideas?

2. Byte data stored as a string. Unicode issues.

3. Errors using RDA Pull on SSCE - Permission Problem

4. Devices and Databases

5. Grantor does not have Permissions Problem

6. Where can I get VFP 3.0 (non Beta)

7. Permissions Problem?

8. Passing empty strings to a parameterized query

9. ADO/SQL70 Stored Procedure permission problems

10. Linked Servers permissions problem

11. Permission problem when Executing the BCP from Analyzer

12. DTS scheduled job failing -- not permissions problem!

13. Who is DTS user under ASP? (Permissions problems)