Recordsource only available to Administrator on clients

[John Hackert]

In my ongoing development of a SQL Server back-end / Access adp front-
end migration from and mdb, I'm encountering an issue I cannot
understand that seems to relate to permissions under the adp.

The system configuration includes a test server running SBS 2003 R2
and two test clients running Win XP SP2.

SQL Server Express 2005 SP2 is installed and working on all machines,
and connectivity is established and stable across the test system for
test users: user logons to windows SBS are successful, and on the
clients in SSMSE all database elements are visible, including the
specific ability to test-execute a stored procedure on the client and
retrieve the expected recordset - including under a test-user logon.
Integrated windows security is used.

Regarding the adp itself, it runs fine on a given client if the client
logon is either The Administrator, or if I change a test user's
permissions to the Administrator Template (from, eg, User Template or
Power User Template). However, when a test user is logged on (again
with seemingly working permissions in SSMSE), recordsets that are
derived from parameterized stored procedures are not populated.

In SSMSE, the test user's default schema is set to "db_owner," and the
database role membership is set to "db_owner." My attempts to adjust
test user permissions short of the administrator level have not
corrected this phenomenon. As one clue, I notice in SSMSE that on the
icon representing the SQLEXPRESS instance, the white circle remains
empty when a client is logged on, whereas the green arrowhead appears
when the Administrator is logged on.

Any insight would be much appreciated - obviously assigning users to
administrator roles is not practical.

 

[Sylvain Lafontaine]

When you say that everything is fine when making your tests inside SSMSE, do
you mean that you are executing them against the local installation of
SQL-Server Express or against the SBS 2003? If in one case, you are working
against a local instance and another case (ADP), you are working against SBS
2003, then no wonder if you got different results.

You say that you are using db_owner as the default schema for the test user:
ADP doesn't work well with anything but dbo as the default schema for all
users and all objects and even then, it's a good idea to set the Record
Source Qualifier of the form explicitely to dbo . If you absolutely want to
use db_owner instead of dbo, then you can try setting the Record Source
Qualifier to db_owner. Of course, using any kind of schema more than one
level deep will also be a good source of problems when accessing the
database from ADP.

I don't understand what you mean with things like The Administrator, the
Administrator Template, the User Template or the Power User Template.

Finally, how are you calling your parameterized stored procedure from the
ADP form?

 

[John Hackert]

Thank you for replying.

To clarify the issues: The one and only SQL Server instance in
question is that on the SBS machine (entitled "SCRS-SERVER
\SQLEXPRESS"). On the SBS machine as well as the clients, the SQL
Server instance is readily connected via SSMSE (Server type: Database
Engine; Server name: SCRS-SERVER\SQLEXPRESS; Authentication: Windows
Authentication). As I noted, all database elements have been visible
in SSMSE, including the stored procedures. However, a consistent
observation is that on the clients after a "successful" connection, if
the empty white circle icon does not change to the green-circle-with-
white-arrowhead form, the problem in question is reproduced.

As I've continued to try to troubleshoot this, without question I have
found that if the client logon has administrator privileges with
respect to SBS, the green circle/white arrowhead appears, and the
problem does not occur in the ADP. When I referenced "The
Administrator," what I meant was the SBS Administrator account. In
the SBS "Server Management" module, if I use "Change User Permissions"
to temporarily set a client user's privileges from, say "User
Template" or "Power User Template" to "Administrator Template," then
logon that user on a client, the SSMSE shows the green circle/white
arrowhead, and the problem is absent. Of further interest, if, while
the user-as-administrator remains logged on to the client, I can
change the user's permissions back to, eg, "User Template" on the SBS,
and the SSMSE on the client continues to show the green+white icon.
When the user logs off and back on (no longer as an administrator),
the problem is reproduced.

On your suggestion I tried setting the clients' "User Mapping" to
"dbo" from "db_owner" for the database in question on the SQL Server
instance in question. I read further on how dbo/db_owner compare and
contrast. Chipman and Baron admonish, "Don't confuse dbo with the
db_owner" fixed database role...." Although I now understand these
terms better, unfortunately making the database role change to "dbo"
did not change the problem. BTW, in the ADP all database items appear
with the recordsource qualifier of "dbo."

Although I observe similar behavior in other forms bound to a
parameterized stored procedure, the particular form in question
("frmPt") has the following recordsource, "dbo.uspPt":

********************************
USE [Patients]
GO
/****** Object: StoredProcedure [dbo].[uspPt] Script Date:
05/29/2007 00:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspPt]
-- Add the parameters for the stored procedure here
@PtID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PtID is null

set @PtID = dbo.usfLastPatient();
-- Insert statements for procedure here

SELECT dbo.tblPt.PtID, dbo.tblPt.ProviderID, dbo.tblPt.OfficeID,
dbo.tblPt.SalutationID, dbo.tblPt.FirstNameID, dbo.tblPt.MiddleNameID,
dbo.tblPt.LastNameID,
dbo.tblPt.SuffixID, dbo.tblPt.MaritalStatusID,
dbo.tblPt.GenderID, dbo.tblPt.BirthDate, dbo.tblPt.SSN,
dbo.tblPt.Admitted, dbo.tblPt.Comments
FROM dbo.tblPt
WHERE (dbo.tblPt.PtID = @PtID)
********************************


As a means to open the single-record form to the last entry,
"dbo.usfLastPatient()" reads as follows:


*********************************************
USE [Patients]
GO
/****** Object: UserDefinedFunction [dbo].[usfLastPatient] Script
Date: 05/29/2007 00:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[usfLastPatient]
(
-- Add the parameters for the function here
--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
DECLARE @LastPtID int

-- Add the T-SQL statements to compute the return value here
SET @LastPtID = (SELECT TOP (1) dbo.tblPt.PtID FROM dbo.tblPt ORDER
BY dbo.tblPt.PtID DESC)

-- Return the result of the function
RETURN (@LastPtID)

END
*********************************************


The above procedures have been working in a stable manner for several
months on a single-machine/single-user prototype, and I'm pretty sure
the same were working on my last formal tests (now some time ago) in a
multi-user environment. The only changes would be that my trial
version of SBS 2003 R2 that I was using melted down and required a re-
installation. I'm getting ready in the next several weeks to fully
implement the system in a new practice.

 

[Sylvain Lafontaine]

The Chipman and Baron comment is about SQL-Server 2000 but with 2005, things
have changed a little. However, if in the ADP all database items appear
with the recordsource qualifier of "dbo." then you should be OK. However,
don't forget to set the Record Source Qualifier to dbo (even if ADP is
displaying all items with dbo. as the schema).

Instead of dbo.uspPt, try using uspPt as the Record Source. Is this doesn't
work, try to use a sql string instead with the EXEC statement before the
name of the SP and the parameters added at the end:

"EXEC uspPT " & FirstParameter & ", " & SecondParameter ...

Finally, if you open the Views/Stored Procedures/Functions Windows in ADP,
are you able to double-click and execute the uspPT stored procedure from
there?

And what about make a direct call using ADO objects?

Finally, what are seeing with the SQL-Server Profiler?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you for replying.

To clarify the issues: The one and only SQL Server instance in
question is that on the SBS machine (entitled "SCRS-SERVER
\SQLEXPRESS"). On the SBS machine as well as the clients, the SQL
Server instance is readily connected via SSMSE (Server type: Database
Engine; Server name: SCRS-SERVER\SQLEXPRESS; Authentication: Windows
Authentication). As I noted, all database elements have been visible
in SSMSE, including the stored procedures. However, a consistent
observation is that on the clients after a "successful" connection, if
the empty white circle icon does not change to the green-circle-with-
white-arrowhead form, the problem in question is reproduced.

As I've continued to try to troubleshoot this, without question I have
found that if the client logon has administrator privileges with
respect to SBS, the green circle/white arrowhead appears, and the
problem does not occur in the ADP. When I referenced "The
Administrator," what I meant was the SBS Administrator account. In
the SBS "Server Management" module, if I use "Change User Permissions"
to temporarily set a client user's privileges from, say "User
Template" or "Power User Template" to "Administrator Template," then
logon that user on a client, the SSMSE shows the green circle/white
arrowhead, and the problem is absent. Of further interest, if, while
the user-as-administrator remains logged on to the client, I can
change the user's permissions back to, eg, "User Template" on the SBS,
and the SSMSE on the client continues to show the green+white icon.
When the user logs off and back on (no longer as an administrator),
the problem is reproduced.

On your suggestion I tried setting the clients' "User Mapping" to
"dbo" from "db_owner" for the database in question on the SQL Server
instance in question. I read further on how dbo/db_owner compare and
contrast. Chipman and Baron admonish, "Don't confuse dbo with the
db_owner" fixed database role...." Although I now understand these
terms better, unfortunately making the database role change to "dbo"
did not change the problem. BTW, in the ADP all database items appear
with the recordsource qualifier of "dbo."

Although I observe similar behavior in other forms bound to a
parameterized stored procedure, the particular form in question
("frmPt") has the following recordsource, "dbo.uspPt":

********************************
USE [Patients]
GO
/****** Object: StoredProcedure [dbo].[uspPt] Script Date:
05/29/2007 00:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspPt]
-- Add the parameters for the stored procedure here
@PtID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PtID is null

set @PtID = dbo.usfLastPatient();
-- Insert statements for procedure here

SELECT dbo.tblPt.PtID, dbo.tblPt.ProviderID, dbo.tblPt.OfficeID,
dbo.tblPt.SalutationID, dbo.tblPt.FirstNameID, dbo.tblPt.MiddleNameID,
dbo.tblPt.LastNameID,
dbo.tblPt.SuffixID, dbo.tblPt.MaritalStatusID,
dbo.tblPt.GenderID, dbo.tblPt.BirthDate, dbo.tblPt.SSN,
dbo.tblPt.Admitted, dbo.tblPt.Comments
FROM dbo.tblPt
WHERE (dbo.tblPt.PtID = @PtID)
********************************


As a means to open the single-record form to the last entry,
"dbo.usfLastPatient()" reads as follows:


*********************************************
USE [Patients]
GO
/****** Object: UserDefinedFunction [dbo].[usfLastPatient] Script
Date: 05/29/2007 00:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[usfLastPatient]
(
-- Add the parameters for the function here
--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
DECLARE @LastPtID int

-- Add the T-SQL statements to compute the return value here
SET @LastPtID = (SELECT TOP (1) dbo.tblPt.PtID FROM dbo.tblPt ORDER
BY dbo.tblPt.PtID DESC)

-- Return the result of the function
RETURN (@LastPtID)

END
*********************************************


The above procedures have been working in a stable manner for several
months on a single-machine/single-user prototype, and I'm pretty sure
the same were working on my last formal tests (now some time ago) in a
multi-user environment. The only changes would be that my trial
version of SBS 2003 R2 that I was using melted down and required a re-
installation. I'm getting ready in the next several weeks to fully
implement the system in a new practice.

 

[Sylvain Lafontaine]

Also, what happens if you replace the code inside the SP with a dummy Select
statement?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you for replying.

To clarify the issues: The one and only SQL Server instance in
question is that on the SBS machine (entitled "SCRS-SERVER
\SQLEXPRESS"). On the SBS machine as well as the clients, the SQL
Server instance is readily connected via SSMSE (Server type: Database
Engine; Server name: SCRS-SERVER\SQLEXPRESS; Authentication: Windows
Authentication). As I noted, all database elements have been visible
in SSMSE, including the stored procedures. However, a consistent
observation is that on the clients after a "successful" connection, if
the empty white circle icon does not change to the green-circle-with-
white-arrowhead form, the problem in question is reproduced.

As I've continued to try to troubleshoot this, without question I have
found that if the client logon has administrator privileges with
respect to SBS, the green circle/white arrowhead appears, and the
problem does not occur in the ADP. When I referenced "The
Administrator," what I meant was the SBS Administrator account. In
the SBS "Server Management" module, if I use "Change User Permissions"
to temporarily set a client user's privileges from, say "User
Template" or "Power User Template" to "Administrator Template," then
logon that user on a client, the SSMSE shows the green circle/white
arrowhead, and the problem is absent. Of further interest, if, while
the user-as-administrator remains logged on to the client, I can
change the user's permissions back to, eg, "User Template" on the SBS,
and the SSMSE on the client continues to show the green+white icon.
When the user logs off and back on (no longer as an administrator),
the problem is reproduced.

On your suggestion I tried setting the clients' "User Mapping" to
"dbo" from "db_owner" for the database in question on the SQL Server
instance in question. I read further on how dbo/db_owner compare and
contrast. Chipman and Baron admonish, "Don't confuse dbo with the
db_owner" fixed database role...." Although I now understand these
terms better, unfortunately making the database role change to "dbo"
did not change the problem. BTW, in the ADP all database items appear
with the recordsource qualifier of "dbo."

Although I observe similar behavior in other forms bound to a
parameterized stored procedure, the particular form in question
("frmPt") has the following recordsource, "dbo.uspPt":

********************************
USE [Patients]
GO
/****** Object: StoredProcedure [dbo].[uspPt] Script Date:
05/29/2007 00:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspPt]
-- Add the parameters for the stored procedure here
@PtID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PtID is null

set @PtID = dbo.usfLastPatient();
-- Insert statements for procedure here

SELECT dbo.tblPt.PtID, dbo.tblPt.ProviderID, dbo.tblPt.OfficeID,
dbo.tblPt.SalutationID, dbo.tblPt.FirstNameID, dbo.tblPt.MiddleNameID,
dbo.tblPt.LastNameID,
dbo.tblPt.SuffixID, dbo.tblPt.MaritalStatusID,
dbo.tblPt.GenderID, dbo.tblPt.BirthDate, dbo.tblPt.SSN,
dbo.tblPt.Admitted, dbo.tblPt.Comments
FROM dbo.tblPt
WHERE (dbo.tblPt.PtID = @PtID)
********************************


As a means to open the single-record form to the last entry,
"dbo.usfLastPatient()" reads as follows:


*********************************************
USE [Patients]
GO
/****** Object: UserDefinedFunction [dbo].[usfLastPatient] Script
Date: 05/29/2007 00:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[usfLastPatient]
(
-- Add the parameters for the function here
--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
DECLARE @LastPtID int

-- Add the T-SQL statements to compute the return value here
SET @LastPtID = (SELECT TOP (1) dbo.tblPt.PtID FROM dbo.tblPt ORDER
BY dbo.tblPt.PtID DESC)

-- Return the result of the function
RETURN (@LastPtID)

END
*********************************************


The above procedures have been working in a stable manner for several
months on a single-machine/single-user prototype, and I'm pretty sure
the same were working on my last formal tests (now some time ago) in a
multi-user environment. The only changes would be that my trial
version of SBS 2003 R2 that I was using melted down and required a re-
installation. I'm getting ready in the next several weeks to fully
implement the system in a new practice.

 

[Sylvain Lafontaine]

And what happens if you replace the code inside the usfLastPatient with a
dummy value? Finally, I'm not sure if you shouldn't state that this
function is not in the same database:

@PtID = Patients.dbo.usfLastPatient();

Do your users have the right to access this second database? I really don't
see the use of a second database here and doing this could be a source of
trouble with permission issues.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you for replying.

To clarify the issues: The one and only SQL Server instance in
question is that on the SBS machine (entitled "SCRS-SERVER
\SQLEXPRESS"). On the SBS machine as well as the clients, the SQL
Server instance is readily connected via SSMSE (Server type: Database
Engine; Server name: SCRS-SERVER\SQLEXPRESS; Authentication: Windows
Authentication). As I noted, all database elements have been visible
in SSMSE, including the stored procedures. However, a consistent
observation is that on the clients after a "successful" connection, if
the empty white circle icon does not change to the green-circle-with-
white-arrowhead form, the problem in question is reproduced.

As I've continued to try to troubleshoot this, without question I have
found that if the client logon has administrator privileges with
respect to SBS, the green circle/white arrowhead appears, and the
problem does not occur in the ADP. When I referenced "The
Administrator," what I meant was the SBS Administrator account. In
the SBS "Server Management" module, if I use "Change User Permissions"
to temporarily set a client user's privileges from, say "User
Template" or "Power User Template" to "Administrator Template," then
logon that user on a client, the SSMSE shows the green circle/white
arrowhead, and the problem is absent. Of further interest, if, while
the user-as-administrator remains logged on to the client, I can
change the user's permissions back to, eg, "User Template" on the SBS,
and the SSMSE on the client continues to show the green+white icon.
When the user logs off and back on (no longer as an administrator),
the problem is reproduced.

On your suggestion I tried setting the clients' "User Mapping" to
"dbo" from "db_owner" for the database in question on the SQL Server
instance in question. I read further on how dbo/db_owner compare and
contrast. Chipman and Baron admonish, "Don't confuse dbo with the
db_owner" fixed database role...." Although I now understand these
terms better, unfortunately making the database role change to "dbo"
did not change the problem. BTW, in the ADP all database items appear
with the recordsource qualifier of "dbo."

Although I observe similar behavior in other forms bound to a
parameterized stored procedure, the particular form in question
("frmPt") has the following recordsource, "dbo.uspPt":

********************************
USE [Patients]
GO
/****** Object: StoredProcedure [dbo].[uspPt] Script Date:
05/29/2007 00:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspPt]
-- Add the parameters for the stored procedure here
@PtID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PtID is null

set @PtID = dbo.usfLastPatient();
-- Insert statements for procedure here

SELECT dbo.tblPt.PtID, dbo.tblPt.ProviderID, dbo.tblPt.OfficeID,
dbo.tblPt.SalutationID, dbo.tblPt.FirstNameID, dbo.tblPt.MiddleNameID,
dbo.tblPt.LastNameID,
dbo.tblPt.SuffixID, dbo.tblPt.MaritalStatusID,
dbo.tblPt.GenderID, dbo.tblPt.BirthDate, dbo.tblPt.SSN,
dbo.tblPt.Admitted, dbo.tblPt.Comments
FROM dbo.tblPt
WHERE (dbo.tblPt.PtID = @PtID)
********************************


As a means to open the single-record form to the last entry,
"dbo.usfLastPatient()" reads as follows:


*********************************************
USE [Patients]
GO
/****** Object: UserDefinedFunction [dbo].[usfLastPatient] Script
Date: 05/29/2007 00:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[usfLastPatient]
(
-- Add the parameters for the function here
--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
DECLARE @LastPtID int

-- Add the T-SQL statements to compute the return value here
SET @LastPtID = (SELECT TOP (1) dbo.tblPt.PtID FROM dbo.tblPt ORDER
BY dbo.tblPt.PtID DESC)

-- Return the result of the function
RETURN (@LastPtID)

END
*********************************************


The above procedures have been working in a stable manner for several
months on a single-machine/single-user prototype, and I'm pretty sure
the same were working on my last formal tests (now some time ago) in a
multi-user environment. The only changes would be that my trial
version of SBS 2003 R2 that I was using melted down and required a re-
installation. I'm getting ready in the next several weeks to fully
implement the system in a new practice.

 

[Sylvain Lafontaine]

Forget about my last comment about the second database, I failed to notice
the second « USE [Patients] » in the code.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

And what happens if you replace the code inside the usfLastPatient with a
dummy value? Finally, I'm not sure if you shouldn't state that this
function is not in the same database:

@PtID = Patients.dbo.usfLastPatient();

Do your users have the right to access this second database? I really
don't see the use of a second database here and doing this could be a
source of trouble with permission issues.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you for replying.

To clarify the issues: The one and only SQL Server instance in
question is that on the SBS machine (entitled "SCRS-SERVER
\SQLEXPRESS"). On the SBS machine as well as the clients, the SQL
Server instance is readily connected via SSMSE (Server type: Database
Engine; Server name: SCRS-SERVER\SQLEXPRESS; Authentication: Windows
Authentication). As I noted, all database elements have been visible
in SSMSE, including the stored procedures. However, a consistent
observation is that on the clients after a "successful" connection, if
the empty white circle icon does not change to the green-circle-with-
white-arrowhead form, the problem in question is reproduced.

As I've continued to try to troubleshoot this, without question I have
found that if the client logon has administrator privileges with
respect to SBS, the green circle/white arrowhead appears, and the
problem does not occur in the ADP. When I referenced "The
Administrator," what I meant was the SBS Administrator account. In
the SBS "Server Management" module, if I use "Change User Permissions"
to temporarily set a client user's privileges from, say "User
Template" or "Power User Template" to "Administrator Template," then
logon that user on a client, the SSMSE shows the green circle/white
arrowhead, and the problem is absent. Of further interest, if, while
the user-as-administrator remains logged on to the client, I can
change the user's permissions back to, eg, "User Template" on the SBS,
and the SSMSE on the client continues to show the green+white icon.
When the user logs off and back on (no longer as an administrator),
the problem is reproduced.

On your suggestion I tried setting the clients' "User Mapping" to
"dbo" from "db_owner" for the database in question on the SQL Server
instance in question. I read further on how dbo/db_owner compare and
contrast. Chipman and Baron admonish, "Don't confuse dbo with the
db_owner" fixed database role...." Although I now understand these
terms better, unfortunately making the database role change to "dbo"
did not change the problem. BTW, in the ADP all database items appear
with the recordsource qualifier of "dbo."

Although I observe similar behavior in other forms bound to a
parameterized stored procedure, the particular form in question
("frmPt") has the following recordsource, "dbo.uspPt":

********************************
USE [Patients]
GO
/****** Object: StoredProcedure [dbo].[uspPt] Script Date:
05/29/2007 00:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspPt]
-- Add the parameters for the stored procedure here
@PtID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PtID is null

set @PtID = dbo.usfLastPatient();
-- Insert statements for procedure here

SELECT dbo.tblPt.PtID, dbo.tblPt.ProviderID, dbo.tblPt.OfficeID,
dbo.tblPt.SalutationID, dbo.tblPt.FirstNameID, dbo.tblPt.MiddleNameID,
dbo.tblPt.LastNameID,
dbo.tblPt.SuffixID, dbo.tblPt.MaritalStatusID,
dbo.tblPt.GenderID, dbo.tblPt.BirthDate, dbo.tblPt.SSN,
dbo.tblPt.Admitted, dbo.tblPt.Comments
FROM dbo.tblPt
WHERE (dbo.tblPt.PtID = @PtID)
********************************


As a means to open the single-record form to the last entry,
"dbo.usfLastPatient()" reads as follows:


*********************************************
USE [Patients]
GO
/****** Object: UserDefinedFunction [dbo].[usfLastPatient] Script
Date: 05/29/2007 00:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[usfLastPatient]
(
-- Add the parameters for the function here
--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
DECLARE @LastPtID int

-- Add the T-SQL statements to compute the return value here
SET @LastPtID = (SELECT TOP (1) dbo.tblPt.PtID FROM dbo.tblPt ORDER
BY dbo.tblPt.PtID DESC)

-- Return the result of the function
RETURN (@LastPtID)

END
*********************************************


The above procedures have been working in a stable manner for several
months on a single-machine/single-user prototype, and I'm pretty sure
the same were working on my last formal tests (now some time ago) in a
multi-user environment. The only changes would be that my trial
version of SBS 2003 R2 that I was using melted down and required a re-
installation. I'm getting ready in the next several weeks to fully
implement the system in a new practice.

 

[John Hackert]

Sylvain,

To follow-up on the troubleshooting steps you suggested:

In the ADP itself, when a connection is established under a non-
Administrator Windows logon, all stored procedures, for example, are
present and accounted for, appearing in the database window in the
format of "uspPt (dbo)", for example. Even though the green circle-
white arrow head is absent in SSMSE, just as in SSMSE the stored
procedure executes - either delivering the last record per the usf, or
a specified PtID if entered via the execution window. However, as I
mentioned before, when the icon in SSMSE is not green, the form does
not populate correctly.

In case this information is helpful, I further noted that the form
DOES display the dbo.tblPt.BirthDate field data and DOES display the
PtID in its field (each text boxes). However, none of the lookup
combo boxes populate (which comprise the majority of the fields on
this form). Further mystifying to me, in design view, all fields show
a warning of "This control has an invalid control source," INCLUDING
the birthdate and ID fields!

The problem is reproduced with an interesting variation by using an
explicit select statement for the recordsource via the form's property
sheet, such as "SELECT PtID, LastNameID, Birthdate FROM dbo.tblPt
WHERE (PtID=1065)" - In form view, the lookup combo does not
populate, as before. In contrast to the native stored procedure case,
however, in the design view none of the fields named in the select
statement show an "invalid control source" error.

I have not learned how to use the SQL profiler yet, so I can't report
on this.

No matter what interesting types of results may be yielded by tweaking
the inputs, the essence of the problem is clearly revealed in SSMSE -
What is the significance of the absence of the green circle/white
arrow in an otherwise "connected" database?, and how is it to be made
green for non-Administrator logons? My interpretation of this problem
is that Windows, via integrated security, is overriding the otherwise-
available SQL Server permissions, effectively preventing the ADP from
running properly.

Another obvious question would be whether I should cut my losses on
the ADP format altogether. The very reason I learned to use SQL
Server was for its advertised multiuser superiority. If Microsoft
will pull the plug on ADPs, surely, even if I was willing to revise
the system to a linked table SQL Server/Access format as seemingly
recommended these days, that configuration will be next on the
deprecation list. Having witnessed the rise and fall of ADO as an
outsider, my guess would be that today's promised land of dotNet will
be tomorrow's regret. It seems that SQL Server should be designed to
have its "own" front for the lowly SQL Server (Express) consumers like
me.

 

[Sylvain Lafontaine]

Two things to check:

First, make sure that there is no multiple Select statements in your SP or
if there are, then make sure that they are OK. One easy way to verify that
would be to add a dummy Select statement at the beginning of the SP (the
same thing that you already wrote for your Record Source as a test).

Second, make sure that there is no other SP, Function, Table or View with
the same name but under a different schema. Again, the use of a dummy
statement should help in verifying that.

 

[John Hackert]

Sylvain,

Of course I'm grateful that you've been willing to try to help me.
Nonetheless, as I mentioned, the SQL Server database and ADP in
question works flawlessly in a single user environment and likewise
works flawlessly in the tested multiuser environment when the users
have Windows administrator privileges.

The phenomena I have described only occur in a multiuser environment
when the client users are restricted to non-administrator privileges
at the Windows level.

If I was observing bizarre behaviors across the board, it would
definitely makes sense to look for things like naming conflicts,
reserved words, etc.

All of what I've described could be considered irrelevant if I could
simply gain some understanding of the significance of the empty white
circle versus the green circle with the white arrowhead in SQL Server
Management Studio Express on a client computer. This observation
would seem to be completely independent of the ADP format and would
therefore seem to be relevant for any user of SQL Server. Perhaps a
Microsoft monitor would be willing to respond to this important issue.

 

[Tom Wimpernark]

they don't need windows administrator priveleges

can't you just give the other windows users the datareader and datawriter
roles and things are peachy?

it sounds to me like you're locking down too much

 

[Tom Wimpernark]

the white circle means 'not connected'

the green arrow means connected

 

[John Hackert]

Tom,

Changing the SQL Server roles from "dbo" to other fixed database roles
such as db_owner, db_datawriter, etc. has not corrected the problem.
According to Sylvain's recommendations, I've now left the role
assignments as "dbo."

In the problem I've been describing, although it may seem impossible,
a test-user on a test-client with non-administrator Windows SBS user
group privileges (eg, "User" or "Power User") can "access" the network
(ie, server-machine's) instance of SQL Server 2005 Express Edition (as
evidenced by being able to view all database element on the database
in question, including the ability to execute stored procedures and
directly modify data in tables) WITHOUT the green dot being
illuminated - even if this status is not considered "connected"

In the ADP if the green dot is not illuminated, the erratic behavior
I've described occurs - surely incidental to the SQL Server/Windows
interface, rather than being a function of Access/ADP features.

If I temporarily change the test-user's "Permissions" in the Windows
SBS "Manage Users" module to "Administrator," at the next test-user
logon the green dot becomes illuminated in SSMSE on the test-client
machine, and the unexpected behavior is absent in the ADP.

 

[John Hackert]

In case anyone continues to follow this thread, of course I've
continued to research this problem. Because the troubleshooting thus
far seems to suggest a permissions problem related to windows
authentication (rather than to sql server or access itself), I tried
to find some information regarding the general topic of windows
authentication in sql server.

There are some threads that seem to suggest that one must in fact be
an administrator under small business server to access sql server from
a client.

If this is the case it would of course seem to jeopardize the security
effort, if users on client machines must be granted administrator
privileges.

I would be grateful if someone with practical experience in these
issues would be willing to respond, or once again if a monitor
representing Microsoft would be willing to explain the proper usage of
the software.