now I will make trigger to prevent user to connect to sql server instance in time between 5 pm to 7 am
and if he want to connect in this time i will save this transaction in database login in my SQL server
i will used datatype sysname
is a built in datatype limited to 128 Unicode characters that, IIRC, is used primarily to store object names when creating scripts. It’s value cannot be NULL (sysname = nvarchar(128) not null)
— Create Login
Create Login Elmasry WITH PASSWORD = ‘egypt’
GO
Create Login Elmasry WITH PASSWORD = ‘egypt’
GO
—Create Database Login_info
CREATE DATABASE Login_Info
GO
USE Login_Info
GO
CREATE TABLE dbo.restrictedlogons
(loginid sysname not null, timestamp datetime not null)
go
use master
go
–Create Trigger
create trigger Prevent_USer_Login
on all server
for logon
as
begin
if original_login () = ‘Elmasry’ and
datepart(hh,getdate()) between 17 and 24
begin
rollback
insert Login_Info.dbo.restrictedlogons
(loginid,timestamp)
values (original_login(),getdate())
end
end
GO
–Drop User Name
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’Elmasry’)
DROP USER Elmasry
Go
—Drop Database
drop database Login_Info
Go
—Disable Trigger
USE Mastre;
GO
DISABLE TRIGGER Prevent_USer_Login ON All Server;
GO
—Drop Trigger
USE MASTER;
GO
DROP TRIGGER Prevent_USer_Login ON ALL SERVER ;
GO