alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.