How to Ban Player By CharName (100% Work)

It took almost three hours doing it!
but worth it
works 100%
You only need to enter the name of the char
time of the ban (I recommend leaving like this)
Ban reason

hey, please Click liked

PHP Code:

---

--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]           
GO
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @CharName VARCHAR(64); 
DECLARE @AccJID INT;           
---------------------------------
SET @CharName =    'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason    =    'Ban Reason Here'
SET @Begin    =    '2012-05-01 00:00:00.000' --    Time Begin    --
SET @End    =    '2040-01-01 00:00:00.000' --    Time End    --
---------------------------------
---------------------------

INSERT [dbo].[_Punishment]
(
[UserJID],
[Type],
[Executor],
[Shard],
[CharName],
[CharInfo],
[PosInfo] ,
[Guide],
[Description],
[RaiseTime],
[BlockStartTime],
[BlockEndTime],
[PunishTime],
[Status]
)
VALUES 
(
@AccJID,
1,
1,
0,
@CharName,
1,
1,
@Reason,
@Reason,
@Begin,
@Begin,
@End,
@End,
0);
----------------------------------------------------------
DECLARE @ReasonID INT
SET @ReasonID =
(Select SerialNo FROM _Punishment Where UserJID = @AccJID)
----------------------------------------------------------
INSERT [dbo].[_BlockedUser]
(
[UserJID],
[UserID],
[Type],
[SerialNo],
[TimeBegin],
[TimeEnd])
VALUES
(
@AccJID,
@ID,
1,
@ReasonID,
@Begin,
@End);

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
-----------------------------------------------------------------
--By Leandro ( nukertube ) -- 


---

Edited ->Querry To Unban Acc By CharName

PHP Code:

---

--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]           
GO
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @CharName VARCHAR(64); 
DECLARE @AccJID INT;           
---------------------------------
SET @CharName =    'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
--------------------------------
USE [SRO_VT_ACCOUNT]
--------------------------------
DELETE FROM [dbo].[_BlockedUser]
WHERE UserJID = @AccJID
DELETE FROM [dbo].[_Punishment]
WHERE UserJID = @AccJID

--By Leandro ( nukertube ) --  

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
----------------------------------------------------------------- 


---

the results box will appear banned player ID to facilitate the Disconect



PLZZ CLICK LIKE

Thank You

:D Ty for Liked

Worked ty ^^

:D Like Plz

like like like

Good Work Thanks

nice what about ban via ip ?

thanks for your work.
A Query to unban a char would be great aswell :)

I still know the basics so shake the sql: D

Querry to Unban Acc added

i'm Need Help to Ban IP
Please Help us

You cud do it like that - Get the IP of the one you want to Ban through the _LogEventChar table - paste the IP's to be banned in a self-created table - rewrite the stored procedure _AddLogChar that IF a char logs on with the IP which exists in the new table run your lil Punishment query to ban this Char directly too.

To have an easier look into the IP's use sth like..

Table:

PHP Code:

---

USE SRO_VT_SHARDLOG

CREATE TABLE _IPLogs (

[No.] int IDENTITY(1,1) PRIMARY KEY,
[CharID] int,
[Charname] varchar(max),
[IP] varchar(max),
[Date] datetime
); 


---

sp -> _AddLogChar

PHP Code:

---

USE [SRO_VT_SHARDLOG]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   procedure [dbo].[_AddLogChar] 
@CharID        int,
@EventID        tinyint,
@Data1        int,
@Data2        int,
@strPos        varchar(64),
@Desc        varchar(128)
as

    IF(@EventID = 4 OR @EventID = 6)
    BEGIN

    declare @len_pos     int
    declare @len_desc    int
    set @len_pos = len(@strPos)
    set @len_desc = len(@Desc)
    if (@len_pos > 0 and @len_desc > 0)
    begin    
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)    
    end
    else if (@len_pos > 0 and @len_desc = 0)
    begin     
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
    end
    else if (@len_pos = 0 and @len_desc > 0)
    begin     
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
    end
    else
    begin
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
    end
    
    --For the new IPLog table
    Declare @DynIP varchar(max);
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE())
    -- END

    END 


---

(I threw every other record than Logins and Logouts out)

so as soon someone logs into the game the correct IP will be parsed into the _IPLogs table in your Log DB - simply put your query into a SP to which you only need to pass e.g. the CharID and a punishment reason, then add it to the _AddLogChar sp to be executed if the...etc. like stated above

Confusing enough? :)

my english is not very good, I would explain further without abbreviations?

Well you could track hereby the IP which you want to be banned and insert it into another Table which contains the banned IP's. Everytime the procedure _AddLogChar will be executed you'll check (a clause in the sp) if the current IP matches any record (EXISTS) in the _BannedIPs table - so if it matches a record, execute your query as a procedure to ban the Char which has logged into with the banned IP as well

e.g. "

PHP Code:

---

IF exists (SELECT IPs FROM _BannedIPs WHERE IPs like @DynIP)
BEGIN
exec BanPlayerSP @CharID
END 


---

"

Thank You

Quote:

---

Originally Posted by Caipi

You cud do it like that - Get the IP of the one you want to Ban through the _LogEventChar table - paste the IP's to be banned in a self-created table - rewrite the stored procedure _AddLogChar that IF a char logs on with the IP which exists in the new table run your lil Punishment query to ban this Char directly too.

To have an easier look into the IP's use sth like..

Table:

PHP Code:

---

USE SRO_VT_SHARDLOG

CREATE TABLE _IPLogs (

[No.] int IDENTITY(1,1) PRIMARY KEY,
[CharID] int,
[Charname] varchar(max),
[IP] varchar(max),
[Date] datetime
); 


---

sp -> _AddLogChar

PHP Code:

---

USE [SRO_VT_SHARDLOG]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   procedure [dbo].[_AddLogChar] 
@CharID        int,
@EventID        tinyint,
@Data1        int,
@Data2        int,
@strPos        varchar(64),
@Desc        varchar(128)
as

    IF(@EventID = 4 OR @EventID = 6)
    BEGIN

    declare @len_pos     int
    declare @len_desc    int
    set @len_pos = len(@strPos)
    set @len_desc = len(@Desc)
    if (@len_pos > 0 and @len_desc > 0)
    begin    
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)    
    end
    else if (@len_pos > 0 and @len_desc = 0)
    begin     
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
    end
    else if (@len_pos = 0 and @len_desc > 0)
    begin     
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
    end
    else
    begin
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
    end
    
    --For the new IPLog table
    Declare @DynIP varchar(max);
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE())
    -- END

    END 


---

(I threw every other record than Logins and Logouts out)

so as soon someone logs into the game the correct IP will be parsed into the _IPLogs table in your Log DB - simply put your query into a SP to which you only need to pass e.g. the CharID and a punishment reason, then add it to the _AddLogChar sp to be executed if the...etc. like stated above

Confusing enough? :)

---

I ran the two queries
would you give me an example of a ban?

like, I wanted to ban the ip of the player called RuN0rDiE
how do I do?

PHP Code:

---

USE SRO_VT_SHARDLOG

Declare @Charname varchar(max);

SET @Charname = 'RuN0rDiE'

SELECT Data2 INTO #temp_table FROM _LogEventChar 
WHERE CharID = (SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharName16 like @Charname) AND (EventID = 4 OR EventID = 6)
GROUP BY Data2
ORDER BY Data2 asc

Declare @Counter int = (SELECT MIN(Data2) FROM #temp_table), @IP varchar(max);

CREATE TABLE #temp_table_result (
[IP] varchar(max)
);

WHILE (@Counter <= (SELECT MAX(Data2) FROM #temp_table))
    BEGIN
        exec @IP = SRO_VT_ACCOUNT.dbo.split_ip @Counter
        INSERT INTO #temp_table_result SELECT @IP
        SET @Counter = (SELECT MIN(Data2) FROM #temp_table WHERE Data2 > @Counter)
    END

SELECT * FROM #temp_table_result

DROP TABLE #temp_table
DROP TABLE #temp_table_result 


---

--> will output all the used IP's of that Character (Only if it wasn't logged into the new _IPLogs table so far)


_BannedIPs table:

PHP Code:

---

CREATE TABLE _BannedIPs (
[No.] int PRIMARY KEY IDENTITY (1,1),
[IP] varchar(max) NOT NULL
); 


---

Your Query as a SP:

PHP Code:

---

USE [SRO_VT_ACCOUNT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_BannPlayerSP]         

@CharName varchar(max),
@Reason varchar(max)

as
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @AccJID INT;           
---------------------------------

SET @ChrID=(SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM SRO_VT_SHARD.dbo._User WHERE CharID=@ChrID)

----------------------

DECLARE 
        @ID VARCHAR(64) = (Select STRuserID FROM TB_User Where JID = @AccJID),
        @Begin DATETIME = GETDATE()-1,
        @End DATETIME = GETDATE()+3650


INSERT [dbo].[_Punishment]
(
[UserJID],
[Type],
[Executor],
[Shard],
[CharName],
[CharInfo],
[PosInfo] ,
[Guide],
[Description],
[RaiseTime],
[BlockStartTime],
[BlockEndTime],
[PunishTime],
[Status]
)
VALUES 
(
@AccJID,
1,
1,
0,
@CharName,
1,
1,
@Reason,
@Reason,
@Begin,
@Begin,
@End,
@End,
0);
----------------------------------------------------------
DECLARE @ReasonID INT
SET @ReasonID =
(Select SerialNo FROM _Punishment Where UserJID = @AccJID)
----------------------------------------------------------
INSERT [dbo].[_BlockedUser]
(
[UserJID],
[UserID],
[Type],
[SerialNo],
[TimeBegin],
[TimeEnd])
VALUES
(
@AccJID,
@ID,
1,
@ReasonID,
@Begin,
@End);

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
----------------------------------------------------------------- 


---

The _AddLogChar procedure which you need for the ban stuff:

PHP Code:

---

USE [SRO_VT_SHARDLOG] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 


ALTER   procedure [dbo].[_AddLogChar]  
@CharID        int, 
@EventID        tinyint, 
@Data1        int, 
@Data2        int, 
@strPos        varchar(64), 
@Desc        varchar(128) 
as 

    IF(@EventID = 4 OR @EventID = 6) 
    BEGIN 

    declare @len_pos     int 
    declare @len_desc    int 
    set @len_pos = len(@strPos) 
    set @len_desc = len(@Desc) 
    if (@len_pos > 0 and @len_desc > 0) 
    begin     
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)     
    end 
    else if (@len_pos > 0 and @len_desc = 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos) 
    end 
    else if (@len_pos = 0 and @len_desc > 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc) 
    end 
    else 
    begin 
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2) 
    end 
     
    --For the new IPLog table 
    Declare @DynIP varchar(max); 
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2 
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE()) 
    -- END
    -- Banned IP stuff
    IF exists (SELECT IP FROM _BannedIPs WHERE IP like @DynIP)
    BEGIN
    Declare @Charname varchar(max) = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
    
    exec SRO_VT_ACCOUNT.dbo._BannPlayerSP @Charname,'usage of a banned IP'
    -- END banned IP stuff
    END

    END 


---

Keep in mind to insert the IP you want to be banned in the new Table _BannedIPs located in the SHARDLOG DB

Tanks For You

Work 100% ban IP !!!!

Thanxs to '@Caipi'

Please Tutorial For All

What kind of a tutorial do you mean? ^^ I explained already everything above in my posts

@onthread
there is already a procedure to do so
@Caipi
you're awesome

Ya'll acting like SQL wud be a challenge to script ^^ thanks, my pleasure

Caipi, you have Querry to Disconect Player??

Caipi

Msg 1046, Level 15, State 1, Procedure _AddLogChar, Line 39
Subqueries are not allowed in this context. Only scalar expressions are allowed.


help pls ?

nukertube is my account here

i need this ; (

Quote:

---

Originally Posted by xxNukertube

nukertube is my account here

i need this ; (

---

You Don't Know why u got Banned

i Know ;D

but i'm Sorry

i Need Unban My Old Account ;P