July 28, 2008 at 9:04 am
Hi All,
I want to know if there is any possibility to restrict Databse Users to login to the database from only a certain servers approved by me? Is this possible in any way?
For example I want to make sure that DB user Test is able to log on from WebServer1 and WebServer2. And that same user should not be able to log on from ApplicationServer1.
Thanks
Roy
-Roy
July 28, 2008 at 10:26 am
If you're running SQL 2005 SP2 or higher, you should be able to do this with a logon trigger. Within the trigger you can check the user name and the host name (or get the net address from sys.dm_exec_connections). If the combination is not acceptable you can rollback that will reject the logon.
Rough, untested code:
CREATE TRIGGER trg_CheckAuthorisedLogins
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF Original_login() IN (<restricted login list goes here> ) AND Host_Name() NOT IN (<Allowed server list goes here > )
BEGIN
PRINT 'Login detected for user ' + Original_login() + ' from host ' + Host_Name() + '. Login rejected' -- Goes into error log
ROLLBACK TRANSACTION
END
END
Couple things to be very careful of. If you refer to a user database in the login trigger and for any reason that user database is not available (offline, detached, suspect, restoring) then ALL logins to the server will fail, including for sysadmin. It's a very quick way to completely lock yourself out of the server.
If you reference any objects within the trigger and the user logging in doesn't have rights to that object, their login will be rejected.
Be careful, test thoroughly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 11:34 am
Thanks GilaMonster. The scary part is Locking yourself out of the DB. I will have to test this out with different scenarios.
-Roy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply