Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Limit Concurrent Logins by database and/or user ID Expand / Collapse
Author
Message
Posted Tuesday, May 1, 2012 12:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1293392
Posted Tuesday, May 1, 2012 12:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1293396
Posted Tuesday, May 1, 2012 3:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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?
Post #1293525
Posted Tuesday, May 1, 2012 3:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1293530
Posted Friday, November 23, 2012 2:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.
Post #1388258
Posted Friday, November 23, 2012 8:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1388268
Posted Monday, November 26, 2012 9:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.
Post #1388695
Posted Tuesday, November 27, 2012 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1389073
Posted Thursday, November 29, 2012 1:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.


  Post Attachments 
Untitled.jpg (2 views, 32.11 KB)
Post #1390828
Posted Monday, December 3, 2012 3:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1392163
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse