Microsoft KB Archive/248772

= PRJ98: SQL Views Required for Multiple UserID Access to a Project =

Article ID: 248772

Article Last Modified on 1/27/2007

-

APPLIES TO


 * Microsoft Project 98 Standard Edition

-



This article was previously published under Q248772



SUMMARY
This article describes how to implement multiple UserID access to a project that has been saved on a Microsoft SQL Server computer. Included in this article are a list of steps and a SQL script that a SQL Server administrator must perform on the SQL Server computer.



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:


 * 1) * 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".
 * 2) * Replace "database" with the name of the database where the project will be saved; for example, "Project98".
 * 3) * Replace "owner" with the name of the database owner; usually this will be DBO.
 * 4) In SQL Server Query Analyzer, run the script you modified.
 * 5) 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

Keywords: KB248772

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.