Re: Limiting views on data in a table

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka_at_avtenta.si)
Date: 06/13/03 

Date: Fri, 13 Jun 2003 10:27:47 +0200

Mike,

Maybe you could help yourself with the SUSER_SNAME() system function - it
returns the Windows login the user uses to connect to SQL Server. You could
use it in the WHERE clause of your single stored procedure for everybody to
compare the current user t the user4 stored in the access table. This way
even if the user comes to the SP directly (s)he won't be able to see
anything but own rows.
BT, the owner of the SP and all of the tables that SP uses must be the same.
Then you can give only Execute permission on the SP. Don't grant or deny any
permissions on the tables. 

-- 
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Lerch" <mlerchNOSPAMTHANKS@nycap.rr.com> wrote in message
news:lkphev8bamv81i36nono3lciusb3oaqk25@4ax.com...
> I have a scenario that I haven't read a solution to.  Imagine that you
> have a table full of national sales info.  You want the field
> salespeople to be able to view reports of their sales info, but they
> shouldn't be able to see other people's stuff.
>
> The way I was implementing this was to use Windows Auth with a Windows
> Group.  For instance, all the sales people would be a member of
> Domain\Sales.  I already have a table that shows which cities are
> covered by which salespeople, an access table.  My thought was to use
> stored procedures that would get passed the username of the
> salesperson.  The procedure would figure out, from the access table,
> what records from the sales table they are able to see, and only those
> records would be returned.  My understanding is that you can deny read
> permission to a table but give execute permission to a stored
> procedure that uses that table...so the salespeople wouldn't be able
> to just open the table even if they wanted to: they have to interact
> with it via the stored procedures.
>
> But here's the rub:  assuming that one of the salespeople knew the
> path to the server, what would stop one of the salespeople from using
> an MS Access Project or for that matter Query Analyzer or Enterprise
> Manager to get into the database and run those stored procedures?
> After all, they have to have access to the database to be able to have
> execute the stored procedures, right?
>
> Please let me know if that is correct and, if so, how to deal with it!
> One thing we DON'T want to do is create separate views for
> everyone...the access list changes all the time as people are added or
> dropped and as territories are shifted around.  Any help is greatly
> appreciated!

Relevant Pages

  • Re: Recordsource only available to Administrator on clients
    ... In the ADP itself, when a connection is established under a non- ... Administrator Windows logon, all stored procedures, for example, are ... It seems that SQL Server should be designed to ...
    (microsoft.public.access.adp.sqlserver)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7
    ... For parsing a string to a date/time, the 103 format doesn't limits the user ... > which is used as the row source for the results form). ... > other solutions assuming I have to use SQL Server 7? ... > I don't understand how I can use parameterized stored procedures to solve ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Scripting T-SQL CREATE Statements
    ... a backup of user-defined SQL Server objects. ... I am having a hard time finding the T-SQL functions and system stored procedures used to return the scripted ... like when you right-click an object and select Script Object to New Window as>> Create. ... I am trying to get it much like the instnwnd.sql file that comes with the .NET Framework SDK v1.1. ...
    (microsoft.public.sqlserver.programming)