skip to main | skip to sidebar

SIVA SQL Server

Adding fuel to my passion on SQL Server

Saturday, May 29, 2010

Deny specific user from Remote Login to SQL Server

SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).

This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger


 

CREATE TRIGGER Deny_Remote_Login_Login1_trigger

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'Login1' AND

(SELECT * FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'Login1' --- Checking the given login

and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely

and Session_ID = @@SPID )

BEGIN

PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally'

ROLLBACK;

END

END;


 

Posted by SIVAPRASAD at 4:36 PM
Labels: Audit, SQL Server 2005, SQL Server 2008, Trigger

No comments:

Post a Comment

Newer Post Older Post Home
Subscribe to: Post Comments (Atom)

Search Siva SQLServer Blog

Labels

SQL 2005 (64) SQL 2008 (40) SQL 2008 R2 (20) Query (14) SQL 2000 (14) SQL Server 2008 (13) SQL 2012 (11) SQL Server 2005 (10) Service Pack (10) DOS Batch (9) Setup (9) Installation (7) Patching (7) Cluster (6) SSIS (6) Tools (6) Audit (5) Performance (5) Windows (5) Download (4) Profiler (4) Reporting Service (4) SQL Server 2000 (4) 64 Bit (3) Connection (3) DBCC (3) Free (3) Learn (3) Linked Server (3) Maintenance Plan (3) Security (3) Service Account (3) Useful (3) CPU (2) Configuration (2) DBA (2) DMV (2) Event Viewer (2) Outlook (2) PASS (2) Replication (2) Restore (2) TSM (2) Terminal Server (2) Trigger (2) AWE (1) Architecture (1) Backup (1) Baseline (1) Best Practices (1) CDC (1) Change Track (1) Checklist (1) Chennai (1) Citrix (1) Clustter (1) Contained Database (1) DMF (1) DTS (1) Database Usage (1) Development (1) Exam Voucher (1) Feature Pack (1) Flowchart (1) HPDP (1) Hotfix (1) Idera (1) Internal (1) Isolation (1) License (1) Log Shipping (1) MOC (1) Normalization (1) Oracle (1) PMP (1) PORT (1) Policy Management (1) Poster (1) Progress (1) Quality (1) Registry (1) SAC (1) SAN (1) SLA (1) SQL Safe (1) SQL Server vs Oracle (1) SQL2000 (1) SRDF (1) Schema (1) Sharepoint - MOSS (1) Shortcutkey (1) Snapshot (1) Spatial (1) Suspect (1) TCP/IP (1) TNG (1) Trace (1) Training (1) Unicenter (1) Uninstall (1) VPN (1) Virtual Lab (1) Visual Studio (1) Whitepaper (1) Wizard (1)

Followers

Blog Archive

  • ►  2014 (2)
    • ►  April (1)
    • ►  February (1)
  • ►  2013 (8)
    • ►  October (2)
    • ►  September (1)
    • ►  August (1)
    • ►  April (3)
    • ►  March (1)
  • ►  2012 (19)
    • ►  December (1)
    • ►  November (6)
    • ►  October (1)
    • ►  September (2)
    • ►  May (2)
    • ►  April (1)
    • ►  March (5)
    • ►  January (1)
  • ►  2011 (18)
    • ►  August (3)
    • ►  July (2)
    • ►  May (10)
    • ►  April (2)
    • ►  March (1)
  • ▼  2010 (47)
    • ►  December (5)
    • ►  November (1)
    • ►  October (7)
    • ►  September (2)
    • ►  August (4)
    • ►  July (4)
    • ►  June (4)
    • ▼  May (6)
      • Query to find when the table last accessed on SQL ...
      • Deny specific user from Remote Login to SQL Server
      • SQL Server 2005 Uninstall Error: The SQL Server Sy...
      • How to remove the subscription from Publication in...
      • How to apply Service pack on SQL Server 2005 Cluster
      • SQL Server 2005 Service Pack 4
    • ►  April (11)
    • ►  February (1)
    • ►  January (2)
  • ►  2009 (30)
    • ►  December (1)
    • ►  November (4)
    • ►  October (2)
    • ►  September (6)
    • ►  May (3)
    • ►  April (3)
    • ►  March (3)
    • ►  February (3)
    • ►  January (5)
  • ►  2008 (53)
    • ►  December (6)
    • ►  November (1)
    • ►  September (3)
    • ►  August (3)
    • ►  July (13)
    • ►  June (9)
    • ►  May (1)
    • ►  April (14)
    • ►  March (1)
    • ►  February (1)
    • ►  January (1)
  • ►  2007 (17)
    • ►  December (5)
    • ►  November (2)
    • ►  August (2)
    • ►  July (3)
    • ►  April (2)
    • ►  March (1)
    • ►  February (2)
  • ►  2006 (13)
    • ►  December (2)
    • ►  August (1)
    • ►  June (3)
    • ►  May (1)
    • ►  April (2)
    • ►  March (3)
    • ►  January (1)
  • ►  2005 (41)
    • ►  December (1)
    • ►  November (2)
    • ►  September (4)
    • ►  August (8)
    • ►  July (5)
    • ►  June (5)
    • ►  May (4)
    • ►  April (11)
    • ►  March (1)

Visitors