3

I have a user login named 'manager' currently logged in PC1, I want to prevent this user from login in PC2. So I created a trigger, and I got an error

Msg 4621, Level 16, State 10, Line 2
Permissions at the server scope can only be granted when the current database is master

It works well if I use database master, but I want to use database Employee. How can I fix it?

USE Employee;
GO

GRANT VIEW SERVER STATE TO manager;
GO

CREATE TRIGGER connection_limit_trigger2
ON ALL SERVER WITH EXECUTE AS 'manager'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'manager' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'manager') > 2
    ROLLBACK;
END;
  • 3
    You can't. You have to use the master database. I think the message is clear. However, this is referring to the grant statement, not to the trigger itself. – Gordon Linoff Dec 4 '15 at 13:18
  • Are you using Windows authentication or SQL Server authentication? Are PC1 and PC2 a SQL Server instance or a domain? – nicomp Dec 4 '15 at 13:53
  • I endorse what @Gordon Linoff said. Also, nicomp's questions are relevant. My own take on this is that you may wish to consider limiting access through another mechanism like a proxy server that sits between the user and the database, or if this is a three tier application - in the application tier. Doing this in the database may not be the best route. Good luck. – Ken Clement Dec 4 '15 at 14:47
  • @ncomp I am using client & server, PC1 and PC2 are clients. @ Ken Clement Do you have another way to limit user login?, I dont care about database, just focus on limit number of user login. – Thế Hiệp Dec 4 '15 at 15:54
1

Logon triggers are special. Not going too far into it, they must exist in the master database and not any other, thus the answer to your question is: "It can't."

https://msdn.microsoft.com/en-us/library/bb326598.aspx

-Sean

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.