PRJ98: SQL Views Required for Multiple UserID Access to a Project (248772)

MORE INFORMATION

When implementing multiple table owner and multiple UserID access permissions to Microsoft SQL Server, the UserID requires views in Microsoft SQL Server that reference tables belonging to the owner of the tables that make up the project. Each view must be the same name as the table it references.

NOTE: The following steps assume that you are an administrator for the SQL Server computer.

To create views on a SQL Server computer, the SQL Server administrator or a UserID with appropriate permissions must follow these steps:

1. Create the project database on the server. Either use the scripts shipped with project (\valuepack\database), or save a database to the server from Microsoft Project.
2. Modify the script below with the following:
   
   • Replace "NTdomain\NTuser" with the domain name (NTdomain) and user name (NTuser) you want to give access to the database; for example, the username JohnD and the NT domain named Sales, "Sales\JohnD".
   • Replace "database" with the name of the database where the project will be saved; for example, "Project98".
   • Replace "owner" with the name of the database owner; usually this will be DBO.
3. In SQL Server Query Analyzer, run the script you modified.
4. Repeat steps 2 and 3 for each user whom you want to grant access to the database.

The user(s) that the script was run for can now open and save to the database.

The included script grants the UserID full access to the database. If you only want to grant Read-Only access, delete all instances of ", insert, update, delete" from the script.

Below is the SQL Script:

--
-- Script to create Project 98 views for use by non-table owner NT users.
-- Globally replace "NTdomain\NTuser", "database" and "owner" as appropriate.
--


-- create the login and user
use master
exec sp_grantlogin @loginame='NTdomain\NTuser'
use [database]
exec sp_grantdbaccess @loginame='NTdomain\NTuser'
go


-- grant permissions on the tables
grant select, insert, update, delete on [owner].Assignment_Actual_Cost to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Actual_Exceptions to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Actual_Ovt_Work to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Actual_Work to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Baseline_Cost to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Baseline_Work to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Information to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Assignment_Remaining_Work to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Calendar_Exceptions to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Calendar_Working_Times to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Calendars to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Custom_Date_Fields to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Custom_Duration_Fields to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Custom_Number_Fields to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Intl_FieldReferences to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Intl_TextConversions to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Project_Information to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_AssignmentPoolInfo to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_CommandBars to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_CustomForms to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_ExternalDataLinks to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_Filters to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_ImportExportMaps to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_Modules to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_Reports to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_Tables to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Reserved_V_iews to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Resource_Baseline_Cost to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Resource_Baseline_Work to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Resource_Information to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Resource_Rates to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Task_Baseline_Cost to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Task_Baseline_Interim_Splits to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Task_Baseline_Work to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Task_Dependencies to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Task_Information to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Task_Percent_Complete to [NTdomain\NTuser]
grant select, insert, update, delete on [owner].Text_Fields to [NTdomain\NTuser]
go


-- create the views on behalf of the user
create view [NTdomain\NTuser].Assignment_Actual_Cost as select * from [owner].Assignment_Actual_Cost
go
create view [NTdomain\NTuser].Assignment_Actual_Exceptions as select * from [owner].Assignment_Actual_Exceptions
go
create view [NTdomain\NTuser].Assignment_Actual_Ovt_Work as select * from [owner].Assignment_Actual_Ovt_Work
go
create view [NTdomain\NTuser].Assignment_Actual_Work as select * from [owner].Assignment_Actual_Work
go
create view [NTdomain\NTuser].Assignment_Baseline_Cost as select * from [owner].Assignment_Baseline_Cost
go
create view [NTdomain\NTuser].Assignment_Baseline_Work as select * from [owner].Assignment_Baseline_Work
go
create view [NTdomain\NTuser].Assignment_Information as select * from [owner].Assignment_Information
go
create view [NTdomain\NTuser].Assignment_Remaining_Work as select * from [owner].Assignment_Remaining_Work
go
create view [NTdomain\NTuser].Calendar_Exceptions as select * from [owner].Calendar_Exceptions
go
create view [NTdomain\NTuser].Calendar_Working_Times as select * from [owner].Calendar_Working_Times
go
create view [NTdomain\NTuser].Calendars as select * from [owner].Calendars
go
create view [NTdomain\NTuser].Custom_Date_Fields as select * from [owner].Custom_Date_Fields
go
create view [NTdomain\NTuser].Custom_Duration_Fields as select * from [owner].Custom_Duration_Fields
go
create view [NTdomain\NTuser].Custom_Number_Fields as select * from [owner].Custom_Number_Fields
go
create view [NTdomain\NTuser].dtproperties as select * from [owner].dtproperties
go
create view [NTdomain\NTuser].Intl_FieldReferences as select * from [owner].Intl_FieldReferences
go
create view [NTdomain\NTuser].Intl_TextConversions as select * from [owner].Intl_TextConversions
go
create view [NTdomain\NTuser].Project_Information as select * from [owner].Project_Information
go
create view [NTdomain\NTuser].Reserved_AssignmentPoolInfo as select * from [owner].Reserved_AssignmentPoolInfo
go
create view [NTdomain\NTuser].Reserved_CommandBars as select * from [owner].Reserved_CommandBars
go
create view [NTdomain\NTuser].Reserved_CustomForms as select * from [owner].Reserved_CustomForms
go
create view [NTdomain\NTuser].Reserved_ExternalDataLinks as select * from [owner].Reserved_ExternalDataLinks
go
create view [NTdomain\NTuser].Reserved_Filters as select * from [owner].Reserved_Filters
go
create view [NTdomain\NTuser].Reserved_ImportExportMaps as select * from [owner].Reserved_ImportExportMaps
go
create view [NTdomain\NTuser].Reserved_Modules as select * from [owner].Reserved_Modules
go
create view [NTdomain\NTuser].Reserved_Reports as select * from [owner].Reserved_Reports
go
create view [NTdomain\NTuser].Reserved_Tables as select * from [owner].Reserved_Tables
go
create view [NTdomain\NTuser].Reserved_V_iews as select * from [owner].Reserved_V_iews
go
create view [NTdomain\NTuser].Resource_Baseline_Cost as select * from [owner].Resource_Baseline_Cost
go
create view [NTdomain\NTuser].Resource_Baseline_Work as select * from [owner].Resource_Baseline_Work
go
create view [NTdomain\NTuser].Resource_Information as select * from [owner].Resource_Information
go
create view [NTdomain\NTuser].Resource_Rates as select * from [owner].Resource_Rates
go
create view [NTdomain\NTuser].Task_Baseline_Cost as select * from [owner].Task_Baseline_Cost
go
create view [NTdomain\NTuser].Task_Baseline_Interim_Splits as select * from [owner].Task_Baseline_Interim_Splits
go
create view [NTdomain\NTuser].Task_Baseline_Work as select * from [owner].Task_Baseline_Work
go
create view [NTdomain\NTuser].Task_Dependencies as select * from [owner].Task_Dependencies
go
create view [NTdomain\NTuser].Task_Information as select * from [owner].Task_Information
go
create view [NTdomain\NTuser].Task_Percent_Complete as select * from [owner].Task_Percent_Complete
go
create view [NTdomain\NTuser].Text_Fields as select * from [owner].Text_Fields
go