Is it possible to assign all schemas to a single login without specifying?-CodePudding

I work with a DB where schemas get added and removed regularly, and preferably I would be able to assign all schemas to a single user with a script.

but as far as I can see, I always have to specify the schema like

ALTER AUTHORIZATION ON SCHEMA::schema_name TO myUser;

Is there a workaround?

CodePudding user response：

DECLARE @sql nvarchar(max) = N'',
  @base nvarchar(max) = N'ALTER AUTHORIZATION ON SCHEMA::$s$ TO myUser;';

SELECT @sql  = REPLACE(@base, N'$s$', QUOTENAME(name))
  FROM sys.schemas;

PRINT @sql;
-- EXEC sys.sp_executesql;