Sending query results to Excel through e-mail in SQL Server

declare @body1 varchar(4000)
declare @body2 varchar(4000)
DECLARE @tab char(1) = CHAR(9)
DECLARE @CRLF char(2)
SELECT @CRLF=CHAR(13)+CHAR(10)

--@query_result_separator=

set @body1 = 'Please find the attached list. '

  +@CRLF+ N' Thanks, SQL DBA Team.'

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='ajaymail',

    @recipients='security@sqlajay.com',
@blind_copy_recipients='dbateam@sqlajay.com',

    @subject = 'DBA database Monthly Report.',

    @body = @body1,

    @body_format = 'HTML',
             
    @query = 'Select USER_ID AS ID, NAME as Username, SECURITY as SecurityPermission, LST_LOGIN AS LastLogin from DBA..table_name',

    @query_result_header = 1,
@query_result_separator =@tab,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'DBA.csv',

    @query_result_no_padding = 1

              

Scripting Out the Database / Object Level Permissions in SQL Server

USE Master  -- Use the required database name here
GO
SET NOCOUNT ON;

PRINT 'USE ['+DB_NAME()+']';
PRINT 'GO'

/********************************************************************************/
/**************** Create a new user and map it with login ***********************/
/********************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Create User Script ***************************/'
PRINT '/*************************************************************/'

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']'
FROM sys.database_principals
WHERE [Type] IN ('U','S')
AND
[NAME] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')

GO
-- Troubleshooting User creation issues
PRINT '/***'+CHAR(10)+
'--Error 15023: User or role <XXXX> is already exists in the database.'+CHAR(10)+
'--Then Execute the below code can fix the issue'+CHAR(10)+
'EXEC sp_change_users_login ''Auto_Fix'',''<Failed User>'''+CHAR(10)+
'GO **/'

/************************************************************************/
/************  Script the User Role Information *************************/
/************************************************************************/

PRINT '/**********************************************************/'
PRINT '/************** Create User-Role Script *******************/'
PRINT '/**********************************************************/'

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBP.NAME + ''''
FROM sys.database_principals DBP
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBP.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id
WHERE DBP.NAME <> 'dbo'

GO

/***************************************************************************/
/************  Script Database Level Permission ****************************/
/***************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Database Level Permission ********************/'
PRINT '/*************************************************************/'

SELECT CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
+ SPACE(1) + DBP.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
+ CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';'
FROM sys.database_permissions AS DBP
INNER JOIN sys.database_principals AS USR ON DBP.grantee_principal_id = USR.principal_id
WHERE DBP.major_id = 0 and USR.name <> 'dbo'
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC


/***************************************************************************/
/************  Script Object Level Permission ******************************/
/***************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Object Level Permission **********************/'
PRINT '/*************************************************************/'

SELECT CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
+ SPACE(1) + DBP.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(OBJ.schema_id)) + '.' + QUOTENAME(OBJ.name)
+ CASE WHEN CL.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(CL.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
+ CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';'
FROM sys.database_permissions AS DBP
INNER JOIN sys.objects AS OBJ ON DBP.major_id = OBJ.[object_id]
INNER JOIN sys.database_principals AS USR ON DBP.grantee_principal_id = USR.principal_id
LEFT JOIN sys.columns AS CL ON CL.column_id = DBP.minor_id AND CL.[object_id] = DBP.major_id
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC

SET NOCOUNT OFF;

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Here’s the script:

-- ************************************************************************************************************************
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
      CASE 
     WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
      + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
     ELSE ' FROM WINDOWS WITH'
    END 
    +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
  AND SL.name NOT LIKE '##%##'
  AND SL.name NOT LIKE 'NT AUTHORITY%'
  AND SL.name NOT LIKE 'NT SERVICE%'
  AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
  THEN SrvPerm.state_desc 
  ELSE 'GRANT' 
 END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
  THEN '' 
  ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> ('sa');

SET NOCOUNT OFF