October 19, 2008 at 12:12 pm
Hi,
Is there a way in MSSQL to authenticate a particular user and server to access a DB.
Like "ABC" user from "10.x.x.x" IP allowed to access "ABCDB"
Please guide me is there any other way to achieve this kind of restriction.
Thanks
Joythi S
October 19, 2008 at 1:19 pm
If you want a user only to be used from a certain address, you should first create your UserId_to_IPAddress table and then use a logon trigger (you need at least SP2 !)
Be very careful with this kind of restrictions !
could be something like:
CREATE TRIGGER S_tr_DBA_ConnectionTracker
ON ALL SERVER
FOR LOGON
execute as owner -- check bol for these options !
AS
BEGIN
set nocount on
If NOT exists (
Select 1
from master.dbo.yourtable T
inner join sys.dm_exec_sessions ES
on ES.[session_id] = @@spid
--and ES.[host_name] = T.[host_name]
--and ES.[program_name] = T.[program_name]
--and ES.[nt_domain] = T.[nt_domain]
--and ES.[nt_user_name] = T.[nt_user_name]
and ES.[login_name] = T.[login_name]
--and ES.[original_login_name] = T.[original_login_name]
inner join sys.dm_exec_connections EC
on EC.[session_id] = @@spid
and EC.[client_net_address] = T.[client_net_address] )
begin
Raiserror('SYSADMIN MESSAGE: Connection Not allowed (s) ',1,1,suser_sname()) with log;
rollback tran
end
END;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2008 at 3:42 pm
Do we need to create the table for every DB. Please advise
October 19, 2008 at 10:27 pm
No. And keep in mind that this logon trigger is only going to fire when they first connect. Once they are in SQL Server, you can't prevent them from changing to a database they have access to.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply