SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Limit Concurrent Logins by database and/or user ID


Limit Concurrent Logins by database and/or user ID

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15057 Visits: 14396
PS I would recommend making LOGIN_ID a SYSNAME and making it the unique clustered index of dbo.LimitedLogins.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15057 Visits: 14396
PPS I would also make NUM_ALLOWED an INT to match the data type that COUNT() returns.

And one other perfromance bump might be to capture the ORIGINAL_LOGIN() in a variable, instead of calling it twice, like so:

USE master
GO
CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS 'logon_trigger_login'
FOR LOGON
AS
BEGIN
DECLARE @original_login SYSNAME = ORIGINAL_LOGIN();

-- the list of logins to restrict
IF (
SELECT NUM_ALLOWED
FROM GK50LIVE.dbo.LimitedLogins
WHERE LOGIN_ID = @original_login
) < (
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = @original_login
)
BEGIN
ROLLBACK
END
END;
GO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 479
thanks OPC I'm going to put this on my test server now. I'll let you know how it turns out.

by the way why do you put the () after ORIGINAL_LOGIN() as if its a function?
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15057 Visits: 14396
kwoznica (5/1/2012)
thanks OPC I'm going to put this on my test server now. I'll let you know how it turns out.

I tested it locally on a 2008R2 instance. Please post back your findings. Thanks.

by the way why do you put the () after ORIGINAL_LOGIN() as if its a function?

I have to...because it is a function. ORIGINAL_LOGIN (Transact-SQL)

This works:
SELECT ORIGINAL_LOGIN()



This throws an exception:
SELECT ORIGINAL_LOGIN



Msg 207, Level 16, State 1, Line 1
Invalid column name 'ORIGINAL_LOGIN'.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 479
OPC,

How could I expand on this so that if a login is not in the login table then the login is allowed. Right now if a login is not in that particular table then the login is denied.

I imagine I would need to add onto the below code


Use master
go
-- create the logon trigger
CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS 'logon_trigger_login'
FOR LOGON
AS
BEGIN
-- the list of logins to restrict
IF (
SELECT NUM_ALLOWED
FROM GK50LIVE.dbo.LimitedLogins
WHERE LOGIN_ID = ORIGINAL_LOGIN()
) < (
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = ORIGINAL_LOGIN()
)
BEGIN
ROLLBACK
END
END;
GO




something like this....



ELSE
COMMIT
END



I couldn't find any ways to allow exceptions in BOL. When I put the commit Else....Commit statement in I receive an error.

Your suggestions are welcomed. thanks.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15057 Visits: 14396
kwoznica (11/23/2012)
OPC,

How could I expand on this so that if a login is not in the login table then the login is allowed. Right now if a login is not in that particular table then the login is denied.

I am on my tablet so I cannot test, but from just reading the code it seems like it should work even if there is no row in the login-table. If there is no row then the selection of NUM_ALLOWED should eval to NULL and any test against NULL will be NULL therefore you should not hit the ROLLBACK. Have you confirmed the trigger works for any low-privilege (i.e. non-sysadmin) logins? I ask because I am wondering if you forgot to include EXECUTE AS 'logon_trigger_login'

COMMIT seems like a natural choice to add as the counter-task to ROLLBACK but it doesn't really work that way. A COMMIT has to be paired with an explicit BEGIN TRANSACTION statement whereas ROLLBACK can be issued without being paired with a BEGIN TRANSACTION because when you are inside a trigger it is implied that you are within, at the very least, an implicit transaction. The ROLLBACK serves as a signal that the current operation should be rolled back, whether that be a login attempt, or in the case of a DML trigger, maybe an UPDATE, DELETE or INSERT.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 479
OPC,

It does work with a non-sysadmin user if they are in the table. I also only tested this at the sql level so I still need to see what happens when I . I actually locked myself out of the database after putting the trigger in place because I did not include my login in the LoginTable and I am part of sysadmin. Fortunately I still had a live connection and the trigger didn't disconnect me so I was able to drop it.

Otherwise if a login is not in the table they are not able to establish a session.

I kept the original code from this post for the trigger. The only changes I made were to the table as per your performance suggestions.

Let me know what you think. In the meantime I'll test a bit further. I appreciate the assistance.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15057 Visits: 14396
It might not be a bad idea to bypass the logic for sysadmins using IS_SRVROLEMEMBER().

E.g.

IF NOT IS_SRVROLEMEMBER ( 'sysadmin', ORIGINAL_LOGIN())
AND ... The check for num allowed in the table

Then rollback.

When you say you tested 'at the sql level' were you by chance using EXECUTE AS?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 479
OPC,

When I tested it at the SQL level I was logging into the sql server as the user in the limitedlogins table.

At the application level I would receive the attached error message.

I then added the principal logon_trigger_login to the sysadmin role and the application error went away. Also I was able to login as a user not in the limited login table, which is the ideal scenario.

I still want to test a few scenarios but with your suggestions this looks to be working.

Do you see any issue with having the logon_trigger_login principal a member of the sysadmin role? Would a lesser role accomplish the same thing?

Thanks for your help.
Attachments
Untitled.jpg (6 views, 32.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15057 Visits: 14396
Sorry for the delay in responding. I just returned from vacation.

kwoznica (11/29/2012)
OPC,

When I tested it at the SQL level I was logging into the sql server as the user in the limitedlogins table.

At the application level I would receive the attached error message.

I then added the principal logon_trigger_login to the sysadmin role and the application error went away. Also I was able to login as a user not in the limited login table, which is the ideal scenario.

Excellent. A working model that proves the concept.

Do you see any issue with having the logon_trigger_login principal a member of the sysadmin role? Would a lesser role accomplish the same thing?

I do not see any issues with it as it relates to the trigger itself but do not recommend having anyone in the sysadmin role that does not technically need it, and this login should not need to be in the sysadmin role so I would recommend to continue until you can once again remove the login from the role. Two other items to check:

1. Did you add a user for the login in the database where the user-table resides, and then grant that database user select permissions on the user-table?

From and earlier post:

USE GK50LIVE;
GO
CREATE USER [logon_trigger_login] FROM LOGIN [logon_trigger_login] WITH DEFAULT_SCHEMA = [dbo];
GO
GRANT SELECT ON dbo.LimitedLogins TO logon_trigger_login;
GO



The login needs access to the user table which implies it must have a user in the database and have permission to select from it.

2. Did you grant the login permissions to view the server-state? From an earlier post:

USE master;
GO
GRANT VIEW SERVER STATE TO logon_trigger_login;
GO



Because we are referring to sys.dm_exec_sessions in the trigger code the executing the trigger must be granted the VIEW SERVER STATE permission.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search