MSSQL Veritabanının özel SCHEMA’larını dbo’ya transfer edip, bağlı olduğu kullanıcıyı tekrar düzenleyen t-sql scriptini aşağıda bulabilirsiniz. Bu script restore edilen veya taşınan veritabanına daha önce tanımlanan kullanıcının bağlanamaması ile alakalı düzenlemeleri yapar. Çözüm sağladığı hata mesajları aşağıdaki gibidir.
Login failed for user ‘username’.
veya
Cannot open database “database” requested by the login. The login failed.
Login failed for user ‘username’.
Login failed for user ‘username’.
-- Author: Oğuzhan YILMAZ, oguzhan@maestropanel.com USE [VERITABANI] GO declare @table_transfer varchar(8000) declare @object_transfer varchar(8000) declare @table varchar(1000) declare @objName varchar(1000) declare @oldschema varchar(1000) declare @newschema varchar(1000) declare @loginName varchar(512) declare @databaseName varchar(512) set @databaseName = 'VERITABANI_ISMI' set @oldschema = 'ESKI_SCHEMA' set @newschema = 'dbo' set @loginName = 'KULLANICI_ADI' WHILE EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) = @oldschema) BEGIN SELECT @table = name FROM sys.tables WHERE object_id IN(SELECT min(object_id) FROM sys.tables WHERE schema_name(schema_id) = @oldschema) SET @table_transfer = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].[' + @table +']' EXEC(@table_transfer) END WHILE EXISTS(SELECT * FROM sys.objects WHERE TYPE IN ('U','V','P','Fn') AND SCHEMA_NAME(schema_id) = @oldschema) BEGIN SELECT @objName = name FROM sys.objects WHERE type IN ('U','V','P','Fn') AND SCHEMA_NAME(schema_id) = @oldschema SET @object_transfer = 'alter schema ' + @newschema + ' transfer [' + @oldschema + '].[' + @objName +']' EXEC(@object_transfer) END IF EXISTS (SELECT Name FROM sys.sysusers WHERE Name = @loginName) BEGIN IF EXISTS (SELECT Name FROM sys.schemas WHERE Name = @oldschema) BEGIN EXEC('ALTER AUTHORIZATION ON SCHEMA::['+ @oldschema +'] TO dbo') EXEC ('DROP SCHEMA ['+ @oldschema +']') END EXEC ('DROP USER ['+ @loginName +']') END EXEC('CREATE USER ['+ @loginName +'] FOR LOGIN ['+ @loginName +']') EXEC('ALTER USER ['+ @loginName +'] WITH DEFAULT_SCHEMA=[dbo]') EXEC sp_addrolemember N'db_owner', @loginName;