Grant all logins select on one table in one database

Question

Grant all logins select on one table in one database

jasona.work

SSC Guru

Points: 50082
More actions
September 28, 2015 at 8:05 am

#303464

I'm trying to implement a logon trigger to restrict the number of simultaneous logons any one user can have. I didn't like the potential implications of granting everyone access to the DMVs, so I'm planning to use an "intermediate" table in a database to hold connection info. Said table will be populated with the current connection counts on a set schedule (and yes, this does mean someone can still exceed the limit.)
Here's my minor issue. I want to keep it simple as far as granting access to the table in question. Ideally, I don't want to have to grant access to the DB / table for each and every login, and have to do this every time a new login is added.
Now, I know this is probably not the best option, but is it possible to grant the server-level public role read-only access to the DB that houses the table?
I'm on SQL2008R2 (with a couple exceptions) so a server-level role that every login belongs too is not (currently) an option, or I'd go that route.
I've done some quick Googling, and not had much luck, but I'm not giving up yet...
Thanks,
Jason
February 6, 2017 at 8:28 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

Will a logon trigger work?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

You can add EXECUTE AS '<somelogin>' to the trigger and this login has been granted access to the table. Or been granted VIEW SERVER STATE. You need to use original_login() to get the name of the actual user. SYSTEM_USER, suser_sname etc will return the name in the EXECUTE AS clause.

However, there are many scenarios where this login trigger will cause cry and agony. If you have an application that uses a middle tier with a proxy login, you will need to make exception for that application login.

And even if you only have plain WinForms application in your organisation, this can still be painful, since users do not have control over how many connections the application opens behind their backs.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

jasona.work SSC Guru Points: 50082 More actions · Answer 3

I hadn't thought about using an execute as, I'll look into that.

The trigger itself does use ORIGINAL_LOGIN() to find the current connection count for the user in the table (trigger's not been implemented yet.)

As for keeping track of the connection count, I've been running a variation of the query I plan to use to populate the table for the last couple weeks, recording the counts and the time it was checked, so I've got a fairly decent general idea as to the max connections someone is using.