"Cannot resolve collation conflict for column 1 in SELECT statement."

SELECT (table1.name   + ' ' + table2.lastname) as ownersname

table1.name and table2.lastname have different collations.

There is a default collation for the server.

There is a default collation for the database (which defaults to the collation for the server)

The default collation for the database will be applied to any column you add to a table UNLESS you explicitly specify a collation at the column level.

/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
GO

/* Find Collation of SQL Server Database Table Column */
/* Address: Table Name
   City:Column Name */
USE AdventureWorks
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
AND name = 'City'

/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO

How to generate script with collation?
(SSMS : Tools : Options : SQL Server object explorer : Scripting : Table and view options : Include collation = True)