Re: With in a SP Truncate dbo.table table-name permissions..
From: Andrew J. Kelly (akelly@targitinteractive.com)
Date: 07/18/02
- Next message: Benjamin Huang: "Contraint validation text not showing when user not in sys admin role"
- Previous message: venkat: "With in a SP Truncate dbo.table table-name permissions.."
- In reply to: venkat: "With in a SP Truncate dbo.table table-name permissions.."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Andrew J. Kelly" <akelly@targitinteractive.com> Date: Thu, 18 Jul 2002 09:45:22 -0400
Directly from BooksOnLine:
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.
-- Andrew J. Kelly SQL MVP Targitinteractive, Inc. "venkat" <vgaddam@us.imshealth.com> wrote in message news:17a1501c22e60$553526c0$2ae2c90a@hosting.microsoft.com... > Hi, > > I have procedure with in the procedure I there is a > Truncate dbo.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. > > SQL Server Books on line: > Qualifying Names Inside Stored Procedures > Inside a stored procedure, object names used with > statements (for example, SELECT or INSERT) that are not > user-qualified default to the owner of the stored > procedure. If a user who creates a stored procedure does > not qualify the name of the tables referenced in SELECT, > INSERT, UPDATE, or DELETE statements within the stored > procedure, access to those tables through the stored > procedure is restricted by default to the creator of the > procedure. > > Object names used with the statements ALTER TABLE, CREATE > TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP > INDEX, UPDATE STATISTICS, and DBCC must be qualified with > the name of the object owner if other users are to use of > the stored procedure. For example, Mary, who owns table > marytab, must qualify the name of her table when it is > used with one of these statements if she wants other users > to be able to execute the stored procedure in which the > table is used. > > This rule is necessary because object names are resolved > when the stored procedure is run. If marytab is not > qualified and John tries to execute the procedure, SQL > Server looks for a table called marytab owned by John. > > > Thanks a millions > Venkat > gaddamv@hotmail.com > 610 834 5241 >
- Next message: Benjamin Huang: "Contraint validation text not showing when user not in sys admin role"
- Previous message: venkat: "With in a SP Truncate dbo.table table-name permissions.."
- In reply to: venkat: "With in a SP Truncate dbo.table table-name permissions.."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
