none
Executing Stored Procedure - Execute As Owner - Failure RRS feed

  • Question

  • Hello SQL Server experts,

    We are running into an issue when trying to execute a stored procedure with the clause execute as Owner. This works in our databases that are running 2014 but not when we upgraded the databases to sql 2019 AG. The database is part of of the AG group and it is synchronized. The trustworthy value is set to false in both 2014 and 2019 instances. The database on both the primary and secondary AG group are owned by sa. This is the error we get is the following:

    Msg 916, Level 14, State 2, Line 3
    The server principal "sa" is not able to access the database "Test_DB" under the current security context.
    Msg 3013, Level 16, State 1, Line 3
    BACKUP DATABASE is terminating abnormally.

    When I print out the commands and run them separately as the sa user the backup db procedure works, however when executed through the procedure it does not. When I remove the Execute as OWNER from the procedure it works but when we leave execute as owner in there it does not. Why?

    USE [DBA]
    GO

    /****** Object:  StoredProcedure [dbo].[backup_devdb]    Script Date: 6/29/2020 4:35:20 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROC [dbo].[backup_devdb]
    @dbName nvarchar(25)
    --WITH EXECUTE AS OWNER -- Removed this line, should investigate why it works when its removed in 2019 but 2014 works as is

    as
    begin

    --declare @dbName nvarchar(25) 
    EXECUTE AS CALLER;


    set @dbName=QUOTENAME(@dbName)

    Declare @backupCmd as varchar(max)
    Declare @verifyCmd as varchar(max)
    declare @email_message as varchar(max)

    Declare @Results as varchar(max)

    declare @allowed as int
    set @allowed= 0

    select @allowed= 1 where upper(@dbName) in (
    'Test_DB')


    IF (@allowed = 1)
    BEGIN
    DECLARE @usr varchar(30)
    SET @usr = SYSTEM_USER

    select @usr = rtrim((ltrim(REPLACE(@usr,'ARC\',''))))

    select @dbname = replace (@dbname,'[','');
    select @dbname = replace (@dbname,']','');
    -- Create the directories for the database backups
    declare @strBackupPath nvarchar(500)
    -- set the root backup folder.
    set @strBackupPath = '\\dev-main\DatabaseBackup'

    -- declare a variable to hold the database name.

    declare @strDatabaseBackupPath nvarchar(500)
    declare @strBackupName nvarchar(500)

    SET @strBackupName =  @dbName + '_backup_' +@usr+'_'+  REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')  + CONVERT(VARCHAR(20), DATEPART(second, GETDATE()), 112) +  '.bak'
    SET @strDatabaseBackupPath = @strBackupPath +'\'+ @strBackupName 
    --print @strBackupName
    --print @strDatabaseBackupPath

    revert;
    select @backupCmd = 'BACKUP DATABASE '+ @dbName +' TO DISK ='''+ @strDatabaseBackupPath +''' WITH  INIT, NAME =''' + @strBackupName +''' ,COPY_ONLY, COMPRESSION'
    print @backupCmd
    exec (@backupCmd)
    print ' '
    select @verifyCmd = 'RESTORE VERIFYONLY FROM DISK = ''' + @strDatabaseBackupPath +''''
    print @verifyCmd
    EXEC (@verifyCmd) 

    select @email_message = 'The user : ' + @usr + ' backed up the database ' + upper(@dbname) + ' to ' + @strDatabaseBackupPath + '.'


    END
    ELSE
    BEGIN
    Print 'You do not have permission to backup '+ @dbName + '.'
    END

    END



    GO

     


    Meachel Carnahan BSc. Computer Science Database Administrator

    Tuesday, June 30, 2020 11:53 PM

All replies

  • Did you try to remove EXEC and creating your backup command dynamically but rather just using variable for @dbName and variable for the DISK (as the syntax seems to allow both to be variables)?

    https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

    ?

    In other words, try excluding extra EXEC (@cmd) in your procedure and see if it helps.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 1, 2020 12:48 AM
    Moderator
  • Hi Meachel Carnahan,

    When impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

    The easy way is SET TRUSTWORTHY ON, but it's not safe, better to use certificate signing.

    See the detailed explanation: Extending Database Impersonation by Using EXECUTE AS.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 1, 2020 6:02 AM
  • When you use EXECUTE AS OWNER you are impersonating a datbase user, and your context is not trusted outside the current database. So you cannot use this procedure to backup another database than the current one. (And not really even that, since BACKUP writes to tables in msdb.)

    As Lily says, a better option is to use certificate signing. This is more secure and more granular. I have written about this technique in detail in an article on my web site: http://www.sommarskog.se/grantperm.html

    By the way, there is no need for dynamic SQL in that procedure, you can do:

    BACKUP DATABASE @dbName TO DISK = @strDatabaseBackupPath WITH  INIT, NAME = @strBackupName,COPY_ONLY, COMPRESSION

    BACKUP and RESTORE accepts variabels for their parameters.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 1, 2020 6:51 AM
  • Hi Meachel Carnahan,

    Do the answers above help you? If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 2, 2020 5:56 AM