May 3, 2011 at 3:37 am
How can i restrict the number of users connected to a database?
May 3, 2011 at 6:57 am
you probablyt want to describe what you are trying to do to get a better answer, as it's a pretty broad question.
This kind of question sometimes comes up due to performance problems, or locked tables or poor perfroamnce, and an incorrect but knee jerk reaction is to want to limit the # of users. Tell us the underlying issue so we can help you better.
As for your question, you cannot limit # users on a per-database issue witha LOGON trigger...only the # of instaces per person or per server.
starting with security, if the logins do not have a user mapped to them, they don't have access.
so potentially you could disable users or roles to prevent access.
you have the ability to restrict access to only sysadmins, or even single user;
ALTER DATABASNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Lowell
May 3, 2011 at 10:25 pm
Lowell (5/3/2011)
As for your question, you cannot limit # users on a per-database issue witha LOGON trigger...only the # of instaces per person or per server.
[/code]
Do you really mean 'cannot' or 'can'?
I just want to restrict logins connected to a db to be restricted. I do not want to do it at server level using user connections options in sp_configure. The same has to be implemented at DB level.
Please let me know perhaps I didnot represent the requirement....
May 4, 2011 at 2:31 am
I will second Lowell for this. actually whats your exact requirement.
meanwhile you can restrict user by limiting their permission at database level like SELECT, EXECUTE etc.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 4, 2011 at 3:07 am
Okei, say, I want to restrict a login to connect less than 5 sessions from SSMS.
I guess, it s possible through LOGON trigger, using rollback checking by the LOGIN_NAME. Am I correct?
May 4, 2011 at 5:08 am
per login = possible.
per database = not possible.
you can restrict the number of connections for a login, but not which databases they touch AFTER they complete the LOGON process; the LOGIN trigger in Books Online does that:
That does not do anything on a per database level...which is why i'm asking you to help us identify the real requirement; my default database might be amster upon connection, but you want to limit access on a specific database, which is a different animal; i do not beleive it can be done.
http://technet.microsoft.com/en-us/library/bb326598.aspx
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply