question

SanjayB avatar image
SanjayB asked

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

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

tsql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Dnirmania avatar image
Dnirmania answered

USE BEGIN, END in your IF statement too.

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 
BEGIN
	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' ;
END


ELSE
BEGIN


	If @Ag_owner=@Login
	BEGIN
		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' ;
	END
	ElSE
	Begin
	
		If @DB_owner = @Login
		BEGIN
			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'
		END
		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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.