0

SQL Server 2019 DBCC CLONEDATABASE runs successfully when issued in a query window ... but when called from a stored procedure, fails with "User 'dbo' does not have permission to run DBCC CLONEDATABASE "

I want to give a user the ability to drop and "rebuild" their database...

The stored procedure is defined with the "EXEC AS Owner" and resides in our _dba_utility database. The "Owner" is a member of the sysadmin server role, and db_owner database role in the _dba_utility database and the database used for cloning..

What am I missing ?

Create procedure [dbo].[usp_rebuild_dev_database] as
Begin

    -- Drop the existing database
    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'dev_database')
    BEGIN
        ALTER DATABASE [dev_database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [dev_database];
    END

    -- Clone the "EMPTY" database
    DBCC CLONEDATABASE ([_EMPTY_dev_database], [dev_database]) WITH VERIFY_CLONEDB;    

    ALTER DATABASE [dev_database] SET READ_WRITE;

    ALTER DATABASE [dev_database] MODIFY FILE ( NAME = N'_EMPTY_dev_database', NEWNAME = N'dev_database' );
    ALTER DATABASE [dev_database] MODIFY FILE ( NAME = N'_EMPTY_dev_database_log', NEWNAME = N'dev_database_log' );

End;

exec _dba_utility.dbo.usp_rebuild_dev_database;

returns.. User 'dbo' does not have permission to run DBCC CLONEDATABASE.

1
  • 1
    "The stored procedure is defined with the ""EXEC AS Owner"". I don't see that un your proc code.
    – Dan Guzman
    Oct 28, 2022 at 18:18

1 Answer 1

0

The "Owner" is a member of the sysadmin server role

Nope. The owner is dbo, a database-level principal.

OWNER

Specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. OWNER cannot be specified for DDL or logon triggers.

CREATE PROCEDURE - EXECUTE AS CLAUSE

3
  • OK, ... changed the EXECUTE AS to EXECUTE AS 'SQLDBA' SQLDBA is a SQL login and a member of the sysadmin role and db_owner role in the all databases.. and I still get the error ... User 'SQLDBA' does not have permission to run DBCC CLONEDATABASE
    – Ollie2308
    Oct 28, 2022 at 16:04
  • That's executing as the user SQLDBA, which again is a database principal. I believe this scenario requires signing the procedure. learn.microsoft.com/en-us/sql/relational-databases/… Oct 28, 2022 at 17:01
  • 1
    @Ollie2308, EXECUTE AS <user> is sandboxed to the current database. Signing the proc as David suggested is the way to go.
    – Dan Guzman
    Oct 28, 2022 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.