RSS

Prevent User to login on sql server

15 Sep

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

 
Leave a comment

Posted by on September 15, 2011 in SQl server Administration

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s