Query/View Privileges

I have a select query that executes properly but when I add the same select
query to a view the view fails with "SELECT permission denied on various
columns".

I have created a view that accesses two databases (same sql instance). The
database owners are different on the two databases.

I have granted column level privileges on two tables that contain sensitive
data and select privileges on two other tables that do not contain senstivie
data.

The user has been given select privileges on the view and has been setup on
the two databases with privileges to the underlying tables.

1) scenerio 1 - select statement ran from Designer window inside sql server
2005
* this query returns the correct results

SELECT R.Network_ID AS supervisor_user_id, S.last_and_first_name AS
supervisor_name, S.employee_number AS supervisor_number,
R.Direct_Reporting AS direct_reporting, E.employee_number, E.surname
AS emp_surname, E.first_name AS emp_first_name, E.initials AS emp_initials
FROM HRCRP4.PWA_MASTER.people AS E INNER JOIN
HRCRP4.PWA_MASTER.career AS ca ON E.unique_identifier =
ca.parent_identifier INNER JOIN
dbo.vw_xlocat AS L ON E.location = L.code INNER JOIN
HRCRP4.PWA_MASTER.people AS S INNER JOIN
dbo.NP_Reporting_Structure AS R ON S.employee_number =
R.Manager_Number ON E.employee_number = R.Employee_Number
WHERE (ca.career_effective_date <= CURRENT_TIMESTAMP) AND
(ca.career_end_date IS NULL OR
ca.career_end_date >= CURRENT_TIMESTAMP)

2) scenario 2 - create view (same as select statment above)

CREATE VIEW [dbo].[VW_Reporting_Structure_nonsensitive]
AS
SELECT R.Network_ID AS supervisor_user_id, S.last_and_first_name AS
supervisor_name, S.employee_number AS supervisor_number,
R.Direct_Reporting AS direct_reporting,
E.employee_number, E.surname AS emp_surname, E.first_name AS emp_first_name,
E.initials AS emp_initials,
FROM HRCRP4.PWA_MASTER.people AS E INNER JOIN
HRCRP4.PWA_MASTER.career AS ca ON E.unique_identifier
= ca.parent_identifier INNER JOIN
dbo.vw_xlocat AS L ON E.location = L.code INNER JOIN
HRCRP4.PWA_MASTER.people AS S INNER JOIN
dbo.NP_Reporting_Structure AS R ON S.employee_number =
R.Manager_Number ON E.employee_number = R.Employee_Number
WHERE (ca.career_effective_date <= CURRENT_TIMESTAMP) AND
(ca.career_end_date IS NULL OR
ca.career_end_date >= CURRENT_TIMESTAMP)


select * from VW_Reporting_Structure_nonsensitive

The select from the view fails. The view fails trying to access columns on
underlying tables that are not even referenced in the view definition.

The first error below 'SELECT permission denied on column
scheduled_end_date' of object 'career', database 'HRCRP4', schema
'PWA_MASTER'. The scheduled end date is not even referenced in the view.

All the errors are related to columns in underlying tables that the view
does not use.

Why is sql trying to access columns on the underlying tables that are not in
the view definition? The errors are associated with the underlying table
that column level privs have been granted on (sensitive data).

If I grant select privilege on the underlying table rather then column level
the view works ok.

Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'scheduled_end_date' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'primary_position' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'career_reason' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'career_description' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'payroll_company_number' of object
'career', database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'generic_job_code' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'job_reference' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'job_title' of object 'career', database
'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'job_grade' of object 'career', database
'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'job_classification' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'union_code' of object 'career', database
'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'position_reference' of object 'career',
database 'HRCRP4', schema 'PWA_MASTER'.
Msg 230, Level 14, State 1, Line 1

.

Relevant Pages

  • Re: Java is becoming the new Cobol
    ... In the Unisys 2200 environment using their Network Database Server, you get a schema work area depending on how you invoke the schema. ... However, it not only copies the schema area into the program, but the "DMCA" (Database Management Communication Area - basically, the block of information that holds your connection state) is copied in too. ... So, you're left with working-storage, which makes it visible to your program only, or common-storage, which makes it visible to programs linked in. ...
    (comp.lang.cobol)
  • Split messages
    ... This is based off the northwind database. ... Create document schema: ... Select the SQL entry, make sure that we are pointing to the SQL ... For the item select a receive pipeline and name it EmpSplitPipe.btp. ...
    (microsoft.public.biztalk.general)
  • Re: Decouple SQL queries from class in OOP design
    ... > Of course there are existing applications that uses many different ... > architectures and still provides measurable business value. ... > should access every database directly. ... by the database, but the schema changes. ...
    (comp.object)
  • Re: SQL Source Control
    ... I know a lot of you use various means for PHP source control (CVS, SVN, ... How do you handle the versioning of the database and data ... let me install and maintain database schemas, ... installs the schema in whatever database I use. ...
    (php.general)
  • Re: xsd files only: enough to start writing app. code of a DB-related component ?
    ... These schema definition files aren't very numerous (about 20 ... EIS database directly, and the database we were interested in was ISAM, and ... If they are good XSD files consider yourselves lucky. ... database schema from an XML schema. ...
    (comp.lang.java.programmer)