DOCUMENT:Q190064  26-AUG-1999  [foxpro]
TITLE   :HOWTO: Build SELECT Statements Based on User Column Privileges
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

When writing SELECT statements that will be executed by users with varying
levels of column privileges on a SQL Server table, the SELECT statements may not
work for all users. For instance, if a user executes a "SELECT * FROM..."
statement and does not have privileges on all columns, the statement will return
an error and no data.

MORE INFORMATION
================

Sample Code
-----------

      *-- Code begins here.
      *-- First, connect as administrator to set the column privileges for
      *-- user "mike".
      *-- This is strictly for demonstration purposes. Production code would
      *-- likely assume that the user login exists and that privileges are
      *-- already set.
      *--
      *-- Change the SQLCONNECT function as needed to connect to
      *-- the database.
      hConnect = SQLCONNECT("MyDSN", "sa", "password")
      IF hConnect <= 0
         =MESSAGEBOX("Connection failed with return code" + STR(hConnect),;
           0, "Connection Error")
         RETURN
      ENDIF

      *-- Grant privileges on three columns to user "mike."
      *-- Code assumes that user "mike" already exists in the pubs database.
      cSQLCommand="GRANT SELECT ON pubs.dbo.authors " + ;
         "(au_id, au_lname, au_fname) TO mike"
      gnExec = SQLEXEC(hConnect, cSQLCommand)

      *-- Disconnect and reconnect as a less privileged user, "mike".
      =SQLDISCONNECT(hConnect)

      *-- Change the SQLCONNECT function as needed to connect to
      *-- the database.
      hConnect = SQLCONNECT("MyDSN", "mike", "password")
      IF hConnect <= 0
         =MESSAGEBOX("Connection failed with return code" + STR(hConnect),;
           0, "Connection Error")
         RETURN
      ENDIF

      *-- Select the pubs database.
      gnExec = SQLEXEC(hConnect, 'use pubs')

      *-- Get the column privileges for the table.
      gnExec = SQLEXEC(hConnect, 'EXEC sp_column_privileges ;
        "authors"', 'privileges')
      SELECT privileges

      *-- Narrow it down to the user we want.
      lsUser = UPPER('mike')
      SELECT Column_Name FROM privileges ;
         WHERE UPPER(Grantee) = lsUser AND Privilege = 'SELECT';
         INTO CURSOR MyPrivileges

      *-- Step through the table and build our SELECT statement.
      SELECT MyPrivileges
      GOTO TOP
      IF NOT EOF() && Assuming user actually has privileges on the table.
                && If the user has no privileges, the table is empty.
         lsColumns = ""
         SCAN
            lsColumns = lsColumns + ALLTRIM(Column_Name) + ", "
         ENDSCAN

         *-- Trim off the last comma and space.
         lsColumns = SUBSTR(lsColumns, 1, LEN(lsColumns)-2)
      ENDIF

      *-- Now issue the select statement.
      gnExec = SQLEXEC(hConnect, 'SELECT ' + lsColumns + ' FROM authors')
      BROWSE
      CLOSE DATA
      SQLDISCONNECT(hConnect)
      *-- Code ends here.

REFERENCES
==========

(c) Microsoft Corporation 1998, All Rights Reserved.
Contributions by Mike Stewart, Microsoft Corporation


Additional query words: kbDSupport kbDSE kbSQL kbVFp300 kbVFp500 kbVFp500a kbVFp600

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.