Prevent multilple logins per user

Question

Prevent multilple logins per user

sqlTobi

Right there with Babe

Points: 739
More actions
April 14, 2008 at 3:27 am

#124773

Hi folks,
I'm looking for a way to prevent multiple logins for users on SQL Server 2005.
Do you know a way how to achieve this?
I think about Service Broker and Event Notifications, but I hope there is an simpler way of doing this.
thanks and regards
tobias

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 1

in SQL2005 you can create triggers that fire on DDL or server events (in addition to earlier DML statement activity) see BOL extract as below.

However I suggest you would NOT want to limit client connections [e.g. from a particular desktop PC], as a user may well have a connection (i.e. SPID at server instance engine end) for ObjectBrowser (left-hand pane in SSMS) as well as actual query window(s) for executing TSQL.

if your intention is to prevent a user having db access from SEVERAL desktops that could be valid, but who's to say which should be allowed/rejected ?

so IMHO a flawed requirement (even if there is a technical solution) !

Dick

Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH [ ,...n ] ]

{ FOR | AFTER } LOGON

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME [ ; ] }

::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

::=

assembly_name.class_name.method_name

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

And if you do decide to go ahead, make sure you exclude any logins used by the SQL engine and SQL agent, and also the cluster service (if it's a clustered server).

Be very careful with login triggers. It is possible (and I've done it twice now) to have a subtle error in a trigger so that it fails for all attempted connections.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Gila, for posterity's sake, were you able to login as DAC when the trigger was preventing normal logins? This could be a very important tip to know, or a real PITA if you couldn't! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

On that server at that time, no, because the server was clustered and the DAC was set as local only. That has since been changed.

I have tested, and you can log in with the DAC if you have a 'broken' login trigger. Test below

-- Warning. This trigger will prevent ALL logins on the server it's created on.

-- This is test code ONLY. Do not run on a production server!!!!

Create Trigger GetOut ON ALL SERVER

FOR LOGON

AS

rollback transaction -- refuse all logins

go

enable trigger GetOut ON ALL SERVER

go

-- drop trigger GetOut ON ALL SERVER

If you create that, all normal connections will fail, but the DAC will still connect.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Thanks for the confirmation Gila, and the test script for it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sqlTobi Right there with Babe Points: 739 More actions · Answer 6

Hi,

thanks to all for answering.

Is there a logon trigger per database instance? I guees not, but I would like to limit the connections only per database, not per server.

So every user can have multiple connections to the server, but only one to my database.

regards

tobias

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

A logon trigger is server wide. I'm not sure if at the point of login you can see what database the user is going to connect to. You'll have to test that.

Also, if the user can change database while connected, you won't pick it up.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass