Sql-server – Minimum permissions required for mail attachment in DbMail

I think that your simplest option is a job.

You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need.

Here's an example based on your requirements:

USE master
GO

CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , CHECK_POLICY = OFF
GO

USE msdb
GO

CREATE USER testUser FOR LOGIN testUser
GO

EXEC sp_addrolemember 'SQLAgentOperatorRole', 'testUser'
GO

DECLARE @jobId uniqueidentifier

EXEC sp_add_job @job_name=N'test', 
        @enabled=1, 
        @owner_login_name=N'sa',
        @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name = @@SERVERNAME


EXEC sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
    @subsystem=N'TSQL', 
    @command=N'DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
SELECT * FROM dbo.sysfiles
SELECT * FROM sys.master_files
SELECT * FROM tempdb.sys.database_files
SELECT * FROM tempdb.sys.dm_db_file_space_usage
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.partitions
SELECT * FROM sys.indexes
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_exec_sessions
'
GO


EXECUTE AS LOGIN = 'testUser'

EXEC msdb.dbo.sp_start_job @job_name = 'test'

REVERT
GO


-- CLEANUP:
-----------
-- EXEC msdb.sys.sp_executesql N'DROP USER testUser'
-- DROP LOGIN testUser
-- EXEC msdb.dbo.sp_delete_job @job_name = 'test'

While granting the SQLAgentOperatorRole is stil a (minor) security concern, I find it less worrying than other solutions.

Assuming you are using SQL Server 2008 and up, A better way of doing is to create a role in the database and grant that role permissions.

You can add users to the role, so they will inherit the permissions of the role.

-- to grant CREATE, ALTER, DROP OBJECTS (tables, procs, functions, views) with ALTER permissions on the schema. You can obviously fine tune below ones as per your needs.

USE db_name;
 CREATE ROLE [new_role] AUTHORIZATION [dbo];
grant alter
      ,delete
      ,execute
      ,insert
      ,references
      ,select
      ,update
      ,view definition
on schema::dbo
to new_role;

grant create table
     ,create procedure
     ,create function
     ,create view
to new_role;

-- Add an existing user to the new role created

  EXEC sp_addrolemember 'new_role', 'DBUserName'
  GO