Im trying to write a code that can change endpoint AG owner DB owner and delete that login. Can anyone help me with what's wrong in this code. I get incorrect syntax near Else error.
But if i run the code in bits it works perfect. when i put them all together i get syntax error. I have put my code here. Thanks in advance.
DECLARE @Login nvarchar(50);
DEclare @Ep_Owner nvarchar(50);
DEclare @Ep_name nvarchar(100);
Declare @Ag_name nvarchar(100);
Declare @Ag_owner nvarchar(100);
Declare @DB_owner nvarchar(100)
set @Login= 'Test' set @Ep_name= (select name FROM sys.database_mirroring_endpoints);
set @Ep_Owner= (select SUSER_NAME(principal_id) FROM sys.database_mirroring_endpoints where name=@Ep_name);
select @Ag_name = ag.name,@Ag_owner=sp.name FROM sys.availability_replicas ar LEFT JOIN sys.server_principals sp ON sp.sid = ar.owner_sid INNER JOIN sys.availability_groups ag ON ag.group_id = ar.group_id WHERE ar.replica_server_name = SERVERPROPERTY('ServerName');
select @DB_owner=name FROM sys.databases where owner_sid = SUSER_SID(@Login);
set @Login= 'Test' set @Ep_name= (select name FROM sys.database_mirroring_endpoints);
set @Ep_Owner= (select SUSER_NAME(principal_id) FROM sys.database_mirroring_endpoints where name=@Ep_name);
IF @Ep_Owner= @Login DECLARE @AlterAuthorizationStatement1 NVARCHAR(MAX);
Declare @sql1 nvarchar(max); set @AlterAuthorizationStatement1 =N'ALTER AUTHORIZATION ON ENDPOINT:: ' + @Ep_name + N' TO sa' EXEC sp_executesql @AlterAuthorizationStatement1;
set @sql1 = 'drop login'+ @Login;
exec sp_executesql @sql1 PRINT'Endpoint owner changed and Login Deleted' ;
ELSE
BEGIN
If @Ag_owner=@Login
DECLARE @AlterAuthorizationStatement2 NVARCHAR(MAX);
Declare @sql2 nvarchar(max);
set @AlterAuthorizationStatement2 =N'ALTER AUTHORIZATION ON AVAILABILITY GROUP:: ' + @Ag_name + N' TO sa'
EXEC sp_executesql @AlterAuthorizationStatement2;
set @sql2 = 'drop login'+ @Login;
exec sp_executesql @sql2 PRINT'AG owner changed and Login Deleted' ;
ElSE
Begin
If @DB_owner = @Login
DECLARE @sql3 NVARCHAR(max);
DECLARE @sql4 NVARCHAR(max);
DEclare @salogin nvarchar(5)
set @salogin='sa'
SET @sql3= 'use Test;
exec sp_changedbowner @loginame = '+@salogin;
set @sql4= N'drop login '+ @Login ;
EXEC sp_executesql @sql3;
EXEC sp_executesql @sql4;
PRINT'DB owner changed and Login deleted'
Else
DECLARE @sql5 NVARCHAR(max);
SET @sql5= N'drop login '+ @Login ;
EXEC sp_executesql @sql5;
PRINT 'User does not own any endpoint and Deleted';
end
end
GO
