November 17, 2016 at 2:09 pm
I am a real newbie to SQL Server. I have a requirement to limit a user's concurrent sessions so I did the following on an existing user 'IDL\selbybe' trying to set the concurrent session max to 3:
USE master;
GO
GRANT VIEW SERVER STATE TO "IDL\selbybe";
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'IDL\selbybe'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()='IDL\selbybe' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'IDL\selbybe') > 3
ROLLBACK;
END;
And it failed with:
Msg 2714, Level 16, State 2, Procedure connection_limit_trigger, Line 1 [Batch Start Line 4]
There is already an object named 'connection_limit_trigger' in the database.
Then in master database I queried:
SELECT * from sys.all_objects where name LIKE '%connecction_limit%';
Query executed successfully but there isn’t an object with a name like connection.
I can't find the object. What am I doing incorrectly?
Thanks,
Beverly
November 17, 2016 at 3:00 pm
logon triggers can be found in sys.server_triggers
SELECT * from sys.server_triggers where name LIKE '%connection_limit%';
November 18, 2016 at 3:33 am
Queries with a CREATE statement can only be executed once. The second time you execute the same query you'll get the error message about the object already exists. If you want to modify an existing object you need to use the ALTER statement (just replace the CREATE with ALTER in your query).
You can also start by checking if the object already exists and drop it. After the object is dropped you can create it again. You can also create a dummy object if it doesn't exist so you can always use the ALTER statement.
Below are samples of both methods (samples are for a stored procedure but can be used for nearly every object):
Drop and (re-)create an object
-- check if the object does exists
if exists(select name from sys.objects where name = 'usp_procedure' and type = 'P' and schema_id = 1)
-- drop the procedure
DROP PROCEDURE dbo.usp_procedure
go
-- create the procedure
CREATE PROCEDURE [dbo].[usp_procedure]
AS
...
Create and alter an object
-- check if the object does not exists
if not exists(select name from sys.objects where name = 'usp_procedure' and type = 'P' and schema_id = 1)
-- create a dummy procedure
exec sp_executesql N'CREATE PROCEDURE dbo.usp_procedure AS select NULL'
go
-- alter the existing (or dummy-) procedure
ALTER PROCEDURE [dbo].[usp_procedure]
AS
...
November 18, 2016 at 3:37 am
If this is a copy & paste, could it just be that typo?
Then in master database I queried:
SELECT * from sys.all_objects where name LIKE '%connecction_limit%';
November 18, 2016 at 8:05 am
Beatrix Kiddo (11/18/2016)
If this is a copy & paste, could it just be that typo?Then in master database I queried:
SELECT * from sys.all_objects where name LIKE '%connecction_limit%';
As it's a server trigger, it doesn't show up in sys.all_objects, a database trigger would though.
(Disclaimer: I'm required to have a similar trigger on my servers, at the server level, and I've been using that to verify I'm right.)
🙂
But having the typo wouldn't help.
November 18, 2016 at 8:58 am
Oh heck. Thanks. Well then I will drop it first then and give it another whirl today. I really appreciate your assistance. I have a feeling that I will be visiting this forum again. Have a terrific weekend.
November 18, 2016 at 9:08 am
Thank you for your assistance. You don't know how much I appreciate your help.
Bev
November 18, 2016 at 9:55 am
beverly.a.selby (11/18/2016)
Thank you for your assistance. You don't know how much I appreciate your help.Bev
You're quite welcome!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply