SQL Server Backup Data By User

Hi @Abdulhakim M. Elrhumi ,

Create a stored procedure backup all databases except system databases and the database with standby state.

USE [master]  
GO   
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO   
-- =============================================  
-- Backup Databases using a stored procedure  
-- =============================================  
CREATE PROCEDURE [dbo].[sp_Backup_Database]   
@name VARCHAR(MAX) = '' -- DB NAME TO CREATE BACKUP  
AS  
BEGIN  
DECLARE @path VARCHAR(256) -- path for backup files    
DECLARE @fileName VARCHAR(256) -- filename for backup    
DECLARE @fileDate VARCHAR(20) -- used for file name  
   
-- specify database backup directory  
SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\'    
   
-- specify filename format  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)   
   
DECLARE db_cursor CURSOR READ_ONLY FOR    
SELECT name   
FROM master.sys.databases   
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases  
AND state = 0 -- database is online  
AND is_in_standby = 0 -- database is not read only for log shipping  
   
OPEN db_cursor     
FETCH NEXT FROM db_cursor INTO @name     
   
WHILE @@FETCH_STATUS = 0     
BEGIN     
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'    
   BACKUP DATABASE @name TO DISK = @fileName    
   FETCH NEXT FROM db_cursor INTO @name     
END     
  
CLOSE db_cursor     
DEALLOCATE db_cursor  
END  
GO  

If you want to force the SP to be executed as a specific user, you can try below;

You can use EXECUTE AS in the SP definition.

CREATE PROCEDURE dbo.MyProcedure  
WITH EXECUTE AS 'domain\user'  
AS  
...  

Alternatively, you can execute only certain commands as the user within the SP:

EXECUTE AS USER = 'Domain\User'  
  <Commands>  
REVERT  

Please refer to the old thread.

Best regards,
Cathy

If the response is helpful, please click "Accept Answer" and upvote it, thank you.