Список таблиц, у которых нет доступа для public

select *
from information_schema.tables t
where table_name not in
(select distinct table_name from information_schema.table_privileges where grantee ='public')
and table_name not in('dtproperties','MSreplication_objects','MSreplication_subscriptions',
'MSsavedforeignkeycolumns','MSsavedforeignkeyextendedproperties','MSsavedforeignkeys',
'MSsnapshotdeliveryprogress')
order by table_name

Изменяем collation таблиц

CREATE PROCEDURE [dbo].[sp_collation] AS

DECLARE @table_name NVARCHAR(100), @column_name NVARCHAR(100), @data_type NVARCHAR(50),
@character_max integer, @sql_select NVARCHAR(1000), @id integer, @column_text NVARCHAR(1000),
@res NVARCHAR(3000), @table_name_old NVARCHAR(100)
CREATE TABLE t(id integer NULL, column_text NVARCHAR(1000) NULL)
CREATE TABLE result(sql NVARCHAR(3000) NULL)

DECLARE sql_script1 CURSOR FOR
SELECT table_name, column_name, data_type,CHARacter_maximum_length
FROM information_schema.columns
WHERE data_type IN('varchar','nvarchar','char','nchar','text','ntext')
--and table_name = 'table_name'

SET @table_name_old =''

OPEN sql_script1
FETCH next FROM sql_script1
INTO @table_name, @column_name, @data_type, @character_max

WHILE @@fetch_status = 0 BEGIN
IF @table_name_old <> @table_name
BEGIN
SET @sql_select = 'Alter table '+RTRIM(@table_name)+' add ID numeric(18,0) NOT NULL IDENTITY (1, 1)'
EXEC(@sql_select)
IF @table_name_old <> ''
BEGIN
INSERT INTO result
SELECT 'Alter table '+RTRIM(@table_name_old)+' drop column ID'
END
END

INSERT INTO result
SELECT 'Alter table '+RTRIM(@table_name)+' alter column '+RTRIM(@column_name) +' '+
RTRIM(@data_type)+'('+RTRIM(CAST(@character_max AS NVARCHAR(5)))+') collate Cyrillic_General_CI_AS'
--print @column_name
SET @sql_select = 'insert into t select id, '+RTRIM(@column_name)+' from '+RTRIM(@table_name)
EXEC(@sql_select)

DECLARE sql_script2 CURSOR FOR
SELECT id,column_text FROM t ORDER BY id
OPEN sql_script2
FETCH next FROM sql_script2 INTO @id, @column_text

WHILE @@fetch_status = 0 BEGIN
IF @column_text IS NOT NULL
BEGIN
INSERT INTO result
SELECT 'update '+RTRIM(@table_name)+' set '+RTRIM(@table_name)+'.'+
RTRIM(@column_name)+'= N'''+RTRIM(@column_text)+''' where id ='+RTRIM(CAST(@id AS NVARCHAR(18)))
IF ((@id+0.00)/2000.00 - CAST((@id+0.00)/2000.00 AS integer) = 0) AND (@id > 1999)
BEGIN
INSERT INTO result
SELECT 'GO'
END
END

FETCH next FROM sql_script2 INTO @id, @column_text
END
DEALLOCATE sql_script2

DELETE FROM t
SET @table_name_old = @table_name
FETCH next FROM sql_script1
INTO @table_name, @column_name, @data_type, @character_max
END
DEALLOCATE sql_script1
INSERT INTO result
SELECT 'Alter table '+RTRIM(@table_name_old)+' drop column ID'

SELECT * FROM result

DROP TABLE t
DROP TABLE result