• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

SQLServer 2008

Hello, I have a SQL login(Nilexadxx) and would like to know if and how I can fix that SQL login to allow only one person at a time to be able to login with it.
0
vera2006
Asked:
vera2006
2 Solutions
 
pritamduttCommented:
Please find below the code to prevent all users from multiple logins.

You could configure this to adapt to a specific user.

Create Trigger GetOut ON ALL SERVER 
FOR LOGON
AS
begin
declare @login_count int
If Exists (
select ses.[login_name] from sys.dm_exec_sessions (nolock) ses
  left join master..sysprocesses (nolock) pro on pro.[spid] =  ses.[session_id]
  where upper(ses.[login_name]) = upper(suser_name())
  group by ses.[login_name]
)
begin

select @login_count = count(*) - 1 from sys.dm_exec_sessions (nolock) ses
    left join master..sysprocesses (nolock) pro on pro.[spid] =  ses.[session_id]
    where upper(ses.[login_name]) = upper(suser_name())
    group by ses.[login_name]
    
 raiserror ('You are already logged on %i time(s) to the system. Access is denied. Contact your System-Administrator.', 18, 1, @login_count)
 rollback transaction -- refuse all logins

return
end
end
go

Open in new window


Hope this helps!
0
 
BartVxCommented:
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now