locked
"dbo" either does not exist or you do not have permission to use it RRS feed

  • Question

  • I have a user on my live server that has select, update, insert permissions on a database..
    I want to the user to execute a procedure that creates some tables and creates a table on the trigger.

    When the user executes the procedure i Get:

    "Msg 2760, Level 16, State 1, Procedure t1, Line 4
    The specified schema name "dbo" either does not exist or you do not
    have permission to use it."

    I can't give alter permission for the user on the dbo schema because the user will be able to alter and drop any table.
    I want the user to be able to create a table only..

    I read on some forums that I should create another schema that would own the procedure and give alter permission for the user on that schema.
     I did that and still got the same error..

    Please give me the solution because it is driving me crazy..
    • Edited by ckumark Friday, June 26, 2009 6:04 PM Fixing thread title bug
    Saturday, June 13, 2009 9:21 AM

All replies

  • You can use EXECUTE AS to elevate permissions for the duration of the CREATE TABLE stored procedure.  For example:
     
    CREATE PROCEDURE dbo.CreateTableFoo
    WITH EXECUTE AS 'dbo'
    AS
    CREATE TABLE dbo.Foo(Bar int NOT NULL)
    GO

    GRANT EXECUTE ON dbo.CreateTableFoo TO LowPrivilegeRole
    GO

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    "wael.kader" wrote in message news
    Saturday, June 13, 2009 11:54 AM
  • It's not enough to create another schema and give the user alter permissions to that schema. You also must either:
     
    Use two-part names in your create table statement and all the statements that use the table, including the name of the schema (a good idea in any case).
    or
    Make the schema you created you this purpose that user's default schema (with ALTER USER...). It's trying to put the table in dbo because that's the user's default schema if you don't specify one. 
     
    For your purposes either one is likely preferrable to escalating permissions.
     
    Cheers,
    Bob Beauchemin
    SQLskills
     
    "wael.kader" wrote in message news:57eaca4e-d394-438 9-acc0-d5abd63a1d73...
    I have a user on my live server that has select, update, insert permissions on a database..
    I want to the user to execute a procedure that creates some tables and creates a table on the trigger.

    When the user executes the procedure i Get:

    "Msg 2760, Level 16, State 1, Procedure t1, Line 4
    The specified schema name "dbo" either does not exist or you do not
    have permission to use it."

    I can't give alter permission for the user on the dbo schema because the user will be able to alter and drop any table.
    I want the user to be able to create a table only..

    I read on some forums that I should create another schema that would own the procedure and give alter permission for the user on that schema.
     I did that and still got the same error..

    Please give me the solution because it is driving me crazy..
    • Proposed as answer by NimitPParikh Saturday, June 13, 2009 4:29 PM
    Saturday, June 13, 2009 4:05 PM
  • You can check the database is case sensitive or not by

    select DATABASEPROPErtyex ('databasename','collation')

    if database is case sensitive then it's more likely you get your mention error.


    Thanks,
    Nimit
    Saturday, June 13, 2009 4:33 PM
  • The problem is that I don't want the tables to be created in the new schema..
    I want them to be owned by dbo..

    How is this possible...Will the "execute as dbo" in the stored procedure work fine ?
    Monday, June 15, 2009 8:30 AM
  • > How is this possible...Will the "execute as dbo" in the stored procedure work fine ?
     
    Yes.  Try the complete script below:
     
    USE tempdb
    GO

    CREATE ROLE LowPrivilegeRole;
    CREATE USER TestPermissions WITHOUT LOGIN;
    EXEC sp_addrolemember 'LowPrivilegeRole','TestPermissions'
    GO
     
    CREATE PROCEDURE dbo.CreateTableFoo
    WITH EXECUTE AS 'dbo'
    AS
    CREATE TABLE dbo.Foo(Bar int NOT NULL);
    GO
    GRANT EXECUTE ON dbo.CreateTableFoo TO LowPrivilegeRole;
    GO

    EXECUTE AS USER = 'TestPermissions';
    GO
    EXECUTE dbo.CreateTableFoo;
    GO
    REVERT;
    GO
    :
     
     
     
     

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    "wael.kader" wrote in message news:bcc4aa4f-6d55-456 1-9926-bdbe5454342e...
    The problem is that I don't want the tables to be created in the new schema..
    I want them to be owned by dbo..

    How is this possible...Will the "execute as dbo" in the stored procedure work fine ?
    Monday, June 15, 2009 12:19 PM