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