Re: Stored procedures + table permissions

Take a look at "permissions" section on BOL's topic (http://msdn.microsoft.com/en-us/library/aa260621%28SQL.80%29.aspx):

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

--
-----------------------------
"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático

http://blogs.solidq.com/es/elrincondeldba


"Bernhard Mayer" <Idonotexisthere@xxxxxxxxxxx> wrote in message news:OFhVZQgBKHA.1336@xxxxxxxxxxxxxxxxxxxxxxx
Dear fellow SQL'ers!

I have a stored procedure 'DoTurnover' which should do a 'truncate table' on two specific tables; how can I achieve to give this procedure the proper permission?

- The application user will log in with the SQL User 'mda'.
- 'mda' does not have the privileges to alter/modify a table structure.
- 'mda' is associated with the role 'db_functions' which has the right to execute the stored procedure 'DoTurnover'

This sould work with SQL Server 2000 and above; can anyone help me here? A T-SQL script would be very nice ... :-)

TIA
Bernhard


.

Relevant Pages

  • Re: With in a SP Truncate dbo.table table-name permissions..
    ... TRUNCATE TABLE permissions default to the table owner, ... According to sql server ... If a user who creates a stored procedure does ...
    (microsoft.public.sqlserver.security)
  • Permissions for executing a stored procedure
    ... Truncate table permissions only default to table owner, ... So, eventhough, you have granted execute permissions to ... Instead using TRUNCATE TABLE, you could use DELETE FROM, ... >We have a stored procedure that has code in it to ...
    (microsoft.public.sqlserver.security)
  • Re: deny truncate?
    ... TRUNCATE TABLE permissions default to the table owner, ... sysadmin fixed server role, and the db_owner and db_ddladmin fixed database ...
    (microsoft.public.sqlserver.programming)
  • Re: deny truncate?
    ... TRUNCATE TABLE permissions default to the table owner, ... sysadmin fixed server role, and the db_owner and db_ddladmin fixed database ...
    (microsoft.public.sqlserver.security)
  • RE: truncate table
    ... this gives you the option to create a stored procedure with a truncate table statement. ... You can then GRANT the stored procedure to be EXECUTED AS a login or role. ... >>Truncate table permissions are NOT transferable. ...
    (microsoft.public.sqlserver.server)
Loading