Limit access for Trusted Login

Question

Limit access for Trusted Login

Simon-220850

SSCrazy

Points: 2789
More actions
January 11, 2006 at 9:14 am

#166683

Hi
We have a 3rd party application with SQL Server as a back-end using SQL Logins. There are a limited amount of people that have Logins to this Application, however I wish to implement reporting on the database available to a wider population based on Trusted Windows connections for a particular Windows Group which I plan to set up for this purpose. Most of this reporting is automation existsing Excel Spreadsheets and Word Docs using VBA. The problem is that really I don't want users connecting via the trusted connection as a member of this windows Group to have access to anything other than Select access to certain tables and views.
Since the Public group (which all users must be a member of) has Select/Update/Delete/Insert rights to virtually all of the user objects in the database, I can't see any way of accomplishing this. Has anybody any ideas ?
Thanks

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Why does the public role have such access? Is this the way the 3rd party application set up permissions?

If not, create a user-defined database role and assign it the permissions that public has but shouldn't. Make the appropriate users (such as from the application) members of that new role.

Create a user-defined database role that has the permissions you want your Windows group to have. Assign the appropriate permissions to it. Then make the Windows group a member of that role.

If you can't do anything about the public role permissions, you do have the ability to use DENY to restrict access. In this case you'd set the user-defined role for that Windows group as DENY for any operations they shouldn't perform. While this is certainly not the ideal solution, it would restrict that Windows group from the operations you don't want the members performing.

K. Brian Kelley
@kbriankelley

Simon-220850 SSCrazy Points: 2789 More actions · Answer 2

Thanks for that Brian, yes unfortunately that's the way the 3rd party has their application set up. Will experiment as you say setting up another role with the necessary access for all of the users in the application

Cheers

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

If that's the way the app works and they won't support any other config, then you'll have to rely on DENY to restrict access through another user defined database role.

K. Brian Kelley
@kbriankelley