Re: User rights problem

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 07/22/05

  • Next message: Dan Guzman: "Re: Case sensitive Password check while logging into SQL Server" 
    Date: Sat, 23 Jul 2005 02:00:49 +0530

    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

    "Gopinath M" <GopinathM@discussions.microsoft.com> wrote in message
    news:BD37896B-98E4-469C-A9F7-71C84F13F8A6@microsoft.com...
    > 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
    >
    >
    >
    >

  • Next message: Dan Guzman: "Re: Case sensitive Password check while logging into SQL Server"

    Relevant Pages

    • Finding/Accessing object when changing of Owner (dbo)
      ... A user "PROC_USER" that is the owner of the stored procedure and function ... The end-users that will access the system will have a specific login like ... user "PROC_USER" in order to allow the stored procedure to access the data. ... the existance of the table in the current schema then it search in the DBO ...
      (microsoft.public.sqlserver.security)
    • Re: Stored Procedure Disappearing
      ... The owner of the sp and all of its component functions is dbo. ... summary report which obtains data from a large stored procedure containing ...
      (microsoft.public.access.reports)
    • Re:Stored Procedure Question
      ... When you execute a SP without any owner name by default ... SQL assumes it to be dbo. ... the stored procedure fire following query. ...
      (microsoft.public.sqlserver.security)
    • Re: object reference in stoed proc
      ... If you don't qualify the object sql server will first look for the object ... as such it will eventually try dbo. ... implications by not qualifying the objects with the owner. ... all object refences dbo by default anyway within a stored procedure? ...
      (microsoft.public.sqlserver.security)
    • Re: xp_logevent and service broker queues
      ... With EXECUTE AS OWNER 'dbo', the database owner of your user database needs to be 'sa' in order for the security context to be 'sa' in master too. ... This queue receives all messages and starts the stored procedure sp_AsyncReceive which is owned by the dbo. ... Maybe somebody can give me a hint how to call 'sysadmin' functions under the dbo user or how to change permissions for calling these functions in a stored procedure which is started by a service broker queue. ...
      (microsoft.public.sqlserver.security)