User rights problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gopinath M - 21 Jul 2005 11:40 GMT

Hi,
I'm have a user called "developer" with rights create procedure,
db_datareader and db_datawrite. When i try to create an sp with owner as dbo
i'm getting the following error

"Specified owner name 'dbo' either does not exist or you do not have
permission to use it."

here is the sample script which i'm using to create the procedure

CREATE PROCEDURE dbo.pGetPatientList
AS
BEGIN
SELECT Name, Age FROM Patient
END.

I would like to create an sp with owner dbo. Can you please tell me the
rights which i should give to the "developer" login for donig so. I cant
grant db_owner rights to the "developer" login(this is to restrict the user
from modifying schema of user tables and contraints).

Regards,
Gopinath

Reply to this Message

Hari Prasad - 22 Jul 2005 21:30 GMT

Hi Gopinath,

You cant give permission to developer to create a stored procedure under the
ownership of DBO. The only option is let your developer create the
stored procedures under his name. Later you can login as SA or user with
db_owner rights and execute the below command to change the owner to dbo.

sp_changeobjectowner 'existingowner.procedurename','dbo'

This will make the object owner to dbo. If needed you can even write a
script and schedule a job to change the owner daily once or twice.

Thanks
Hari
SQL Server MVP

> Hi,
> I'm have a user called "developer" with rights create procedure,
[quoted text clipped - 22 lines]
> Regards,
> Gopinath

Reply to this Message