If you need to connect to Deltek via some ODBC connection but do not want to show all. I suggest creating a restricted SQL View like this one we use for limited Employee information. If you allow this view read access via SQL authentication and a SQL login you can safely restict the user’s access. From the SQL management console you can create the view (use xxVW_ as a naming convention to steer around any others there – where xx is the abbreviation of your company or something consistent)
Create VIEW [dbo].[xxVW_FilteredEmployee]
AS
SELECT Employee, FirstName, MiddleName, LastName, Status, Org, EMail
FROM dbo.EM
– Grant Access to your SQL login
Grant Select on [xxVW_FilteredEmployee] To DeltekUserR
On a separate idea – If you want a Stored Procedure that allows search parms to be ignored if not present here is an example (more on how this may fit in & be used later)
Create PROCEDURE [dbo].[xxSP_searchEmployee]
(
@FirstName nvarchar(255)
, @LastName nvarchar(255)
, @Supervisor nvarchar(255)
)
AS
BEGIN
SET NOCOUNT OFF;
SELECT Employee, FirstName, MiddleName, LastName, Status, Org, EMail, Supervisor
FROM EM
WHERE (EM.FirstName LIKE CASE When @FirstName is null THEN EM.FirstName ELSE @FirstName + ‘%’ END )
AND (EM.LastName LIKE CASE When @LastName is null THEN EM.LastName ELSE @LastName + ‘%’ END )
AND (EM.Supervisor = CASE When @Supervisor is null THEN EM.Supervisor ELSE @Supervisor END )
END
– Grant Access to your SQL login
Grant Execute on [xxSP_searchEmployee] To DeltekUserR
You can test by submitting this query
xxSP_searchEmployee, d,g,null :: which will wildcard first name starting with D and last name with G ignoring the supervisor.
