full permission for store procedures sql 2005

Dear Expert,

How to give full permission for store procedure in sql 2005.
For example,

user: ABC\lynn
for full store procedure permission, I should go to sproc, property and
click user, check all of grant for full permission?
Please advise.

Thanks,
Lynn
yrcdba7Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
hi all

first of all Lynn

your instinct is currect.
if you want the user to totaly control the procedure (alter,drop, etc)
the yes just grant all the available permissions on the procedure to the specific user.

if you want the user to have execution privileges on the  procedure then just grant him Execute on the specific procedure.


@Lowfatspread im sorry but you are wrong regarding the ALL privilage.

Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
A .Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
B .Stored procedure permissions: EXECUTE.
C .Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
D .View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

0
 
LowfatspreadCommented:
do you just want them to be able to use the procedure?

Grant Exec spname to user


full (create/drop/alter and run)

grant all spname to user
0
 
LowfatspreadCommented:
thanks for the reminder
0
All Courses

From novice to tech pro — start learning today.