none
Can CREATE but not GRANT EXECUTE RRS feed

  • Question

  • I CREATEd a stored PROCEDURE in my own schema and GRANTed EXECUTE without error. I then also CREATEd it in the dbo schema without error, yet when i try to GRANT EXECUTE on it:

    Msg 15151, Level 16, State 1, Line 1
    Cannot find the object '<object_name>', because it does not exist or you do not have permission.


    Monday, November 23, 2009 4:25 PM
    Moderator

Answers

  • Brian, when I ran mine I found the following extra permissions in addition to what you posted.

    TAKE OWNERSHIP
    ALTER
    CONTROL.

    It looks like you don't have full control on the dbo schema. I think someone with full ownership needs to grant you execute on the stored procedure and add WITH GRANT, this way you can grant access to any one.


    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 5:48 PM

All replies

  • Do you have the permission to grant yourself? BOL says the grantor has to have the permission to issue the GRANT.
    http://technet.microsoft.com/en-us/library/ms188371.aspx
    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 4:35 PM
  • I thought since i CREATEd it, i automatically could GRANT EXECUTE on it.
    Monday, November 23, 2009 4:47 PM
    Moderator
  • How do i check who has permission to GRANT on an object?
    Monday, November 23, 2009 5:08 PM
    Moderator
  • Check out this link. You can check the permissions you have on the database level,

    http://msdn.microsoft.com/en-us/library/ms176097.aspx
    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 5:17 PM
  • Thanx. What i'm discovering here is that i don't know enough about SQL Server permissions. My database permissions are:

    SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE');

    CREATE TABLE
    CREATE VIEW
    CREATE PROCEDURE
    CREATE FUNCTION
    CREATE RULE
    CREATE DEFAULT
    CREATE TYPE
    CREATE ASSEMBLY
    CREATE XML SCHEMA COLLECTION
    CREATE SCHEMA
    CREATE SYNONYM
    CREATE AGGREGATE
    CREATE MESSAGE TYPE
    CREATE SERVICE
    CREATE CONTRACT
    CREATE REMOTE SERVICE BINDING
    CREATE ROUTE
    CREATE QUEUE
    CREATE SYMMETRIC KEY
    CREATE ASYMMETRIC KEY
    CREATE FULLTEXT CATALOG
    CREATE CERTIFICATE
    CREATE DATABASE DDL EVENT NOTIFICATION
    CONNECT
    CHECKPOINT
    SHOWPLAN
    ALTER ANY SCHEMA
    ALTER ANY ASSEMBLY
    ALTER ANY DATASPACE
    ALTER ANY MESSAGE TYPE
    ALTER ANY CONTRACT
    ALTER ANY SERVICE
    ALTER ANY REMOTE SERVICE BINDING
    ALTER ANY ROUTE
    ALTER ANY FULLTEXT CATALOG
    ALTER ANY SYMMETRIC KEY
    ALTER ANY ASYMMETRIC KEY
    ALTER ANY CERTIFICATE
    SELECT
    INSERT
    UPDATE
    DELETE
    REFERENCES
    EXECUTE
    ALTER ANY DATABASE DDL TRIGGER
    ALTER ANY DATABASE EVENT NOTIFICATION
    VIEW DEFINITION

    For a test:

    ALTER USER [domain\username] WITH DEFAULT_SCHEMA = [domain\username];
    GO
    CREATE PROCEDURE A AS SELECT 1;
    GO
    SELECT permission_name FROM fn_my_permissions('A', 'OBJECT');

    REFERENCES
    EXECUTE
    VIEW DEFINITION
    ALTER
    TAKE OWNERSHIP
    CONTROL

    ALTER USER [domain\username] WITH DEFAULT_SCHEMA = dbo;
    GO
    CREATE PROCEDURE A AS SELECT 1;
    GO
    SELECT permission_name FROM fn_my_permissions('A', 'OBJECT');

    REFERENCES
    EXECUTE
    VIEW DEFINITION
    ALTER

    Apparently, i can CREATE a PROCEDURE in dbo but it isn't mine. I'm confused by this part. Why should the SCHEMA i CREATE it in change the permissions?
    Monday, November 23, 2009 5:30 PM
    Moderator
  • Brian, when I ran mine I found the following extra permissions in addition to what you posted.

    TAKE OWNERSHIP
    ALTER
    CONTROL.

    It looks like you don't have full control on the dbo schema. I think someone with full ownership needs to grant you execute on the stored procedure and add WITH GRANT, this way you can grant access to any one.


    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 5:48 PM
  • Thanx for helping.
    Monday, November 23, 2009 5:51 PM
    Moderator