locked
CREATE PROCEDURE permission denied in database (I could drop the procedure but could not create) RRS feed

  • Question

  • Hello Forum Members,

    I got the above error when I tried to create a SPROC. I did drop and create Sproc...The Sproc was dropped and I got the above

    error 'Create Procedure permission denied in database'.

    Please advise me.

    Sqlquery9

    • Moved by Sofiya Li Wednesday, May 14, 2014 2:14 AM the right forum
    Tuesday, May 13, 2014 1:30 AM

Answers

  • Hi sqlquery,

    According to your description and error message, when you want to create a Transact-SQL stored procedure by using SQL Server Management Studio and by using the Transact-SQL CREATE PROCEDURE statement, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being createdThis permission is granted implicitly to the db_ddladmin and db_owner fixed database roles.

    You can use the following example to grant create procedure permission to user. Note that the grantor must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.

    grant create procedure to [username]

    For more information, see: GRANT Database Permissions (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms178569.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Neha Mahajan DBA Wednesday, May 14, 2014 3:50 AM
    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:17 AM
    Wednesday, May 14, 2014 2:42 AM
  • As per it is clear create permissions procedure needed to the user

    GRANT CREATE PROCEDURE TO [username]

    http://technet.microsoft.com/en-us/library/ms345415.aspx#Permissions


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:17 AM
    Wednesday, May 14, 2014 3:50 AM

All replies

  • Hi sqlquery,

    According to your description and error message, when you want to create a Transact-SQL stored procedure by using SQL Server Management Studio and by using the Transact-SQL CREATE PROCEDURE statement, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being createdThis permission is granted implicitly to the db_ddladmin and db_owner fixed database roles.

    You can use the following example to grant create procedure permission to user. Note that the grantor must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.

    grant create procedure to [username]

    For more information, see: GRANT Database Permissions (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms178569.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Neha Mahajan DBA Wednesday, May 14, 2014 3:50 AM
    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:17 AM
    Wednesday, May 14, 2014 2:42 AM
  • As per it is clear create permissions procedure needed to the user

    GRANT CREATE PROCEDURE TO [username]

    http://technet.microsoft.com/en-us/library/ms345415.aspx#Permissions


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:17 AM
    Wednesday, May 14, 2014 3:50 AM
  • If u get Db_Owner permission then it will be great 

    else only for creating proc u want 

    grant create procedure to [username]

    if u find this post Helpful please mark as Answer/Helpful

    Vivek

    Wednesday, May 14, 2014 6:29 AM