ADP/SQL Server 2000 Security Problem

[MikeC]

I just migrated a functioning MSDE 2000 database to SQL Server 2000 and now
the non-admin users are unable to connect to the production server. The ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any desktop,
but other users are unable to connect from the *same* desktop machines. The
error message is:

"[MyApp] could not log on to the server. Verify that the log on information
is correct."

The File/Connection option does not display for these users, but when I log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any problem
at all...probably because I'm an administrator on the server. The only
other user who has been able to successfully connect to the server using the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed at
the test server (MSDE 2000), then the user can go into File/Connection and
change the server to the production server. However, if the user clicks on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider. Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem to
find it. When I go into Enterprise Manager on the production server, the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned. When I
display the object permissions, *none* of the boxes are checked. I'm not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should be
checked. If they are supposed to be checked, then why are they not checked?
Is there something special I need to do to re-activate the security
permissions?

....or am I on the wrong track and need to check something else?

 

[Sylvain Lafontaine]

And how did you achieve this the MSDE 2000 database? By following the same
settings, you should get the same result. My opinion is that you have
created SQL-Server User Accounts instead of Windows Accounts on the
SQL-Server. With Windows Accounts, the name of the machine should be
displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to interactively
logon on this machine. However, if you have used the same machine as the
MSDE 2000 database, then there should be no difference there.

If you have used roles, then it's normal that individual permissions remains
unchecked for users. Only the permissions that you specifically set for a
user, independant of those granted by its associated roles, will be
displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

I just migrated a functioning MSDE 2000 database to SQL Server 2000 and now
the non-admin users are unable to connect to the production server. The
ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any desktop,
but other users are unable to connect from the *same* desktop machines.
The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when I
log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any problem
at all...probably because I'm an administrator on the server. The only
other user who has been able to successfully connect to the server using
the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed at
the test server (MSDE 2000), then the user can go into File/Connection and
change the server to the production server. However, if the user clicks
on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem to
find it. When I go into Enterprise Manager on the production server, the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned. When
I
display the object permissions, *none* of the boxes are checked. I'm not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should be
checked. If they are supposed to be checked, then why are they not
checked?
Is there something special I need to do to re-activate the security
permissions?

...or am I on the wrong track and need to check something else?

 

[MikeC]

The actual user account was 'AD\Pchapin' in the example mentioned in the
original post. The account names display the same way
('AD\[WindowsAccount]') in Enterprise Manager.

Originally, login access was granted to individual users using a stored
procedure...and it worked fine. At least, it worked in MSDE 2000. The
actual stored procedure is at the bottom of this message. (Security roles
were successfully assigned using a different procedure.)

Also, I have not created any new accounts for the production database. We
are continuing to use the accounts that were previously created in MSDE
2000. Other than SA, there are no SQL Server user accounts. All users have
Windows accounts.

The SQL Server and MSDE 2000 instances of the database are on separate
machines. All users should have access to both machines. I believe that
the users can access the SQL Server machine because it is already being used
for other production databases, like Sharepoint Portal. However, I must be
missing something because no one can connect!

One thing that may or may not be relevant is that I was the dbo in the
original database and someone else is the dbo in the production database. I
still have administrator permissions, but I am not the dbo. Would this have
any effect on user security? I seem to be running out of clues. Is there
anything else I should check?

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ALTER PROCEDURE stp_SecGrantLoginDBAccess
@LoginName nvarchar(128)
AS

SET NOCOUNT ON

SET @LoginName = 'AD\' + @LoginName

EXEC dbo.sp_grantlogin @LoginName

EXEC dbo.sp_grantdbaccess @LoginName

EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'

SET NOCOUNT OFF

RETURN
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

And how did you achieve this the MSDE 2000 database? By following the
same settings, you should get the same result. My opinion is that you
have created SQL-Server User Accounts instead of Windows Accounts on the
SQL-Server. With Windows Accounts, the name of the machine should be
displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to
interactively logon on this machine. However, if you have used the same
machine as the MSDE 2000 database, then there should be no difference
there.

If you have used roles, then it's normal that individual permissions
remains unchecked for users. Only the permissions that you specifically
set for a user, independant of those granted by its associated roles, will
be displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
now
the non-admin users are unable to connect to the production server. The
ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any
desktop,
but other users are unable to connect from the *same* desktop machines.
The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when I
log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any
problem
at all...probably because I'm an administrator on the server. The only
other user who has been able to successfully connect to the server using
the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed at
the test server (MSDE 2000), then the user can go into File/Connection
and
change the server to the production server. However, if the user clicks
on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem to
find it. When I go into Enterprise Manager on the production server, the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned.
When I
display the object permissions, *none* of the boxes are checked. I'm not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should be
checked. If they are supposed to be checked, then why are they not
checked?
Is there something special I need to do to re-activate the security
permissions?

...or am I on the wrong track and need to check something else?

 

[Sylvain Lafontaine]

The owner of the dbo account shouldn't have any effect.

If the second machine is a WinXP machine, then you can check is if users
logon as themselves or as guests.

You can try to set a SQL-Server account and logon with it. You can try to
use Enterprise Manager to setup the account and see if the domain AD is
recognised by EM. Finally, you can take a look at what happens with a
backup/restore or a detach/attach from the MSDE to the SQL-Server machine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

The actual user account was 'AD\Pchapin' in the example mentioned in the
original post. The account names display the same way
('AD\[WindowsAccount]') in Enterprise Manager.

Originally, login access was granted to individual users using a stored
procedure...and it worked fine. At least, it worked in MSDE 2000. The
actual stored procedure is at the bottom of this message. (Security roles
were successfully assigned using a different procedure.)

Also, I have not created any new accounts for the production database. We
are continuing to use the accounts that were previously created in MSDE
2000. Other than SA, there are no SQL Server user accounts. All users
have Windows accounts.

The SQL Server and MSDE 2000 instances of the database are on separate
machines. All users should have access to both machines. I believe that
the users can access the SQL Server machine because it is already being
used for other production databases, like Sharepoint Portal. However, I
must be missing something because no one can connect!

One thing that may or may not be relevant is that I was the dbo in the
original database and someone else is the dbo in the production database.
I still have administrator permissions, but I am not the dbo. Would this
have any effect on user security? I seem to be running out of clues. Is
there anything else I should check?

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ALTER PROCEDURE stp_SecGrantLoginDBAccess
@LoginName nvarchar(128)
AS

SET NOCOUNT ON

SET @LoginName = 'AD\' + @LoginName

EXEC dbo.sp_grantlogin @LoginName

EXEC dbo.sp_grantdbaccess @LoginName

EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'

SET NOCOUNT OFF

RETURN
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

And how did you achieve this the MSDE 2000 database? By following the
same settings, you should get the same result. My opinion is that you
have created SQL-Server User Accounts instead of Windows Accounts on the
SQL-Server. With Windows Accounts, the name of the machine should be
displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to
interactively logon on this machine. However, if you have used the same
machine as the MSDE 2000 database, then there should be no difference
there.

If you have used roles, then it's normal that individual permissions
remains unchecked for users. Only the permissions that you specifically
set for a user, independant of those granted by its associated roles,
will be displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
now
the non-admin users are unable to connect to the production server. The
ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any
desktop,
but other users are unable to connect from the *same* desktop machines.
The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when I
log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any
problem
at all...probably because I'm an administrator on the server. The only
other user who has been able to successfully connect to the server using
the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed
at
the test server (MSDE 2000), then the user can go into File/Connection
and
change the server to the production server. However, if the user clicks
on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem to
find it. When I go into Enterprise Manager on the production server,
the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned.
When I
display the object permissions, *none* of the boxes are checked. I'm
not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should be
checked. If they are supposed to be checked, then why are they not
checked?
Is there something special I need to do to re-activate the security
permissions?

...or am I on the wrong track and need to check something else?

 

[MikeC]

The server is running Windows 2003. Also, the connection problem occurs on
different client machines running Win2K and WinXP. I'll check whether users
are connecting to the server as guests. I had assumed they were not logging
in as guests, but that may not be a safe assumption at this point.

I originally added the database by detaching it from MSDE 2000 and then
using the Attach option in Enterprise Manager.

I'll also test using a SQL Server account and see what happens. Thanks for
the ideas.

The owner of the dbo account shouldn't have any effect.

If the second machine is a WinXP machine, then you can check is if users
logon as themselves or as guests.

You can try to set a SQL-Server account and logon with it. You can try to
use Enterprise Manager to setup the account and see if the domain AD is
recognised by EM. Finally, you can take a look at what happens with a
backup/restore or a detach/attach from the MSDE to the SQL-Server machine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

The actual user account was 'AD\Pchapin' in the example mentioned in the
original post. The account names display the same way
('AD\[WindowsAccount]') in Enterprise Manager.

Originally, login access was granted to individual users using a stored
procedure...and it worked fine. At least, it worked in MSDE 2000. The
actual stored procedure is at the bottom of this message. (Security
roles were successfully assigned using a different procedure.)

Also, I have not created any new accounts for the production database.
We are continuing to use the accounts that were previously created in
MSDE 2000. Other than SA, there are no SQL Server user accounts. All
users have Windows accounts.

The SQL Server and MSDE 2000 instances of the database are on separate
machines. All users should have access to both machines. I believe that
the users can access the SQL Server machine because it is already being
used for other production databases, like Sharepoint Portal. However, I
must be missing something because no one can connect!

One thing that may or may not be relevant is that I was the dbo in the
original database and someone else is the dbo in the production database.
I still have administrator permissions, but I am not the dbo. Would this
have any effect on user security? I seem to be running out of clues. Is
there anything else I should check?

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ALTER PROCEDURE stp_SecGrantLoginDBAccess
@LoginName nvarchar(128)
AS

SET NOCOUNT ON

SET @LoginName = 'AD\' + @LoginName

EXEC dbo.sp_grantlogin @LoginName

EXEC dbo.sp_grantdbaccess @LoginName

EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'

SET NOCOUNT OFF

RETURN
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

And how did you achieve this the MSDE 2000 database? By following the
same settings, you should get the same result. My opinion is that you
have created SQL-Server User Accounts instead of Windows Accounts on the
SQL-Server. With Windows Accounts, the name of the machine should be
displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to
interactively logon on this machine. However, if you have used the same
machine as the MSDE 2000 database, then there should be no difference
there.

If you have used roles, then it's normal that individual permissions
remains unchecked for users. Only the permissions that you specifically
set for a user, independant of those granted by its associated roles,
will be displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
now
the non-admin users are unable to connect to the production server.
The ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any
desktop,
but other users are unable to connect from the *same* desktop machines.
The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when I
log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any
problem
at all...probably because I'm an administrator on the server. The only
other user who has been able to successfully connect to the server
using the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed
at
the test server (MSDE 2000), then the user can go into File/Connection
and
change the server to the production server. However, if the user
clicks on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem
to
find it. When I go into Enterprise Manager on the production server,
the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned.
When I
display the object permissions, *none* of the boxes are checked. I'm
not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should be
checked. If they are supposed to be checked, then why are they not
checked?
Is there something special I need to do to re-activate the security
permissions?

...or am I on the wrong track and need to check something else?

 

[Sylvain Lafontaine]

The call to the stp_SecGrantLoginDBAccess procedure have been done after or
before the reattachment of the database?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

The server is running Windows 2003. Also, the connection problem occurs
on different client machines running Win2K and WinXP. I'll check whether
users are connecting to the server as guests. I had assumed they were not
logging in as guests, but that may not be a safe assumption at this point.

I originally added the database by detaching it from MSDE 2000 and then
using the Attach option in Enterprise Manager.

I'll also test using a SQL Server account and see what happens. Thanks
for the ideas.

The owner of the dbo account shouldn't have any effect.

If the second machine is a WinXP machine, then you can check is if users
logon as themselves or as guests.

You can try to set a SQL-Server account and logon with it. You can try
to use Enterprise Manager to setup the account and see if the domain AD
is recognised by EM. Finally, you can take a look at what happens with a
backup/restore or a detach/attach from the MSDE to the SQL-Server
machine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

The actual user account was 'AD\Pchapin' in the example mentioned in the
original post. The account names display the same way
('AD\[WindowsAccount]') in Enterprise Manager.

Originally, login access was granted to individual users using a stored
procedure...and it worked fine. At least, it worked in MSDE 2000. The
actual stored procedure is at the bottom of this message. (Security
roles were successfully assigned using a different procedure.)

Also, I have not created any new accounts for the production database.
We are continuing to use the accounts that were previously created in
MSDE 2000. Other than SA, there are no SQL Server user accounts. All
users have Windows accounts.

The SQL Server and MSDE 2000 instances of the database are on separate
machines. All users should have access to both machines. I believe
that the users can access the SQL Server machine because it is already
being used for other production databases, like Sharepoint Portal.
However, I must be missing something because no one can connect!

One thing that may or may not be relevant is that I was the dbo in the
original database and someone else is the dbo in the production
database. I still have administrator permissions, but I am not the dbo.
Would this have any effect on user security? I seem to be running out
of clues. Is there anything else I should check?

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ALTER PROCEDURE stp_SecGrantLoginDBAccess
@LoginName nvarchar(128)
AS

SET NOCOUNT ON

SET @LoginName = 'AD\' + @LoginName

EXEC dbo.sp_grantlogin @LoginName

EXEC dbo.sp_grantdbaccess @LoginName

EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'

SET NOCOUNT OFF

RETURN
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message And how did you achieve this the MSDE 2000 database? By following the
same settings, you should get the same result. My opinion is that you
have created SQL-Server User Accounts instead of Windows Accounts on
the SQL-Server. With Windows Accounts, the name of the machine should
be displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to
interactively logon on this machine. However, if you have used the same
machine as the MSDE 2000 database, then there should be no difference
there.

If you have used roles, then it's normal that individual permissions
remains unchecked for users. Only the permissions that you
specifically set for a user, independant of those granted by its
associated roles, will be displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
now
the non-admin users are unable to connect to the production server.
The ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any
desktop,
but other users are unable to connect from the *same* desktop
machines. The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when
I log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any
problem
at all...probably because I'm an administrator on the server. The
only
other user who has been able to successfully connect to the server
using the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed
at
the test server (MSDE 2000), then the user can go into File/Connection
and
change the server to the production server. However, if the user
clicks on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem
to
find it. When I go into Enterprise Manager on the production server,
the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned.
When I
display the object permissions, *none* of the boxes are checked. I'm
not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should
be
checked. If they are supposed to be checked, then why are they not
checked?
Is there something special I need to do to re-activate the security
permissions?

...or am I on the wrong track and need to check something else?

 

[MikeC]

I executed the stored procedure for two individual users after I attached
the database. The roles also appear to be assigned to the users when I
display their security information in EM.

The call to the stp_SecGrantLoginDBAccess procedure have been done after or
before the reattachment of the database?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

The server is running Windows 2003. Also, the connection problem occurs
on different client machines running Win2K and WinXP. I'll check whether
users are connecting to the server as guests. I had assumed they were not
logging in as guests, but that may not be a safe assumption at this point.

I originally added the database by detaching it from MSDE 2000 and then
using the Attach option in Enterprise Manager.

I'll also test using a SQL Server account and see what happens. Thanks
for the ideas.

The owner of the dbo account shouldn't have any effect.

If the second machine is a WinXP machine, then you can check is if users
logon as themselves or as guests.

You can try to set a SQL-Server account and logon with it. You can try
to use Enterprise Manager to setup the account and see if the domain AD
is recognised by EM. Finally, you can take a look at what happens with a
backup/restore or a detach/attach from the MSDE to the SQL-Server
machine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


The actual user account was 'AD\Pchapin' in the example mentioned in the
original post. The account names display the same way
('AD\[WindowsAccount]') in Enterprise Manager.

Originally, login access was granted to individual users using a stored
procedure...and it worked fine. At least, it worked in MSDE 2000. The
actual stored procedure is at the bottom of this message. (Security
roles were successfully assigned using a different procedure.)

Also, I have not created any new accounts for the production database.
We are continuing to use the accounts that were previously created in
MSDE 2000. Other than SA, there are no SQL Server user accounts. All
users have Windows accounts.

The SQL Server and MSDE 2000 instances of the database are on separate
machines. All users should have access to both machines. I believe
that the users can access the SQL Server machine because it is already
being used for other production databases, like Sharepoint Portal.
However, I must be missing something because no one can connect!

One thing that may or may not be relevant is that I was the dbo in the
original database and someone else is the dbo in the production
database. I still have administrator permissions, but I am not the dbo.
Would this have any effect on user security? I seem to be running out
of clues. Is there anything else I should check?

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ALTER PROCEDURE stp_SecGrantLoginDBAccess
@LoginName nvarchar(128)
AS

SET NOCOUNT ON

SET @LoginName = 'AD\' + @LoginName

EXEC dbo.sp_grantlogin @LoginName

EXEC dbo.sp_grantdbaccess @LoginName

EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'

SET NOCOUNT OFF

RETURN
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message And how did you achieve this the MSDE 2000 database? By following the
same settings, you should get the same result. My opinion is that you
have created SQL-Server User Accounts instead of Windows Accounts on
the SQL-Server. With Windows Accounts, the name of the machine should
be displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to
interactively logon on this machine. However, if you have used the same
machine as the MSDE 2000 database, then there should be no difference
there.

If you have used roles, then it's normal that individual permissions
remains unchecked for users. Only the permissions that you
specifically set for a user, independant of those granted by its
associated roles, will be displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
now
the non-admin users are unable to connect to the production server.
The ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any
desktop,
but other users are unable to connect from the *same* desktop
machines. The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when
I log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any
problem
at all...probably because I'm an administrator on the server. The
only
other user who has been able to successfully connect to the server
using the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed
at
the test server (MSDE 2000), then the user can go into File/Connection
and
change the server to the production server. However, if the user
clicks on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem
to
find it. When I go into Enterprise Manager on the production server,
the
users appear in the "Users" folder for the database. When I look at a
user's properties, the correct security roles appear to be assigned.
When I
display the object permissions, *none* of the boxes are checked. I'm
not
sure whether this is correct. Since specific object permissions are
assigned via the roles, I don't know whether any of the boxes should
be
checked. If they are supposed to be checked, then why are they not
checked?
Is there something special I need to do to re-activate the security
permissions?

...or am I on the wrong track and need to check something else?

 

[MikeC]

I just realized that when I originally ran the stored procedure, I did not
run the other stored procedure to revoke security in advance. (It is also
possible that I ran the store procedure against the wrong account, but I
don't believe so.)

I just now revoked security from one user and then granted login access,
etc. using my other procedure. Now the user can connect to the production
SQL Server. I repeated the process for another user that was unable to
connect and that user can also connect now.

Now, I'll revoke/re-grant security to all of the users that I have a
workable solution. Thanks again for your input.

I executed the stored procedure for two individual users after I attached
the database. The roles also appear to be assigned to the users when I
display their security information in EM.

The call to the stp_SecGrantLoginDBAccess procedure have been done after or
before the reattachment of the database?

with

a

backup/restore or a detach/attach from the MSDE to the SQL-Server
machine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


The actual user account was 'AD\Pchapin' in the example mentioned in the
original post. The account names display the same way
('AD\[WindowsAccount]') in Enterprise Manager.

Originally, login access was granted to individual users using a stored
procedure...and it worked fine. At least, it worked in MSDE 2000. The
actual stored procedure is at the bottom of this message. (Security
roles were successfully assigned using a different procedure.)

Also, I have not created any new accounts for the production database.
We are continuing to use the accounts that were previously created in
MSDE 2000. Other than SA, there are no SQL Server user accounts. All
users have Windows accounts.

The SQL Server and MSDE 2000 instances of the database are on separate
machines. All users should have access to both machines. I believe
that the users can access the SQL Server machine because it is already
being used for other production databases, like Sharepoint Portal.
However, I must be missing something because no one can connect!

One thing that may or may not be relevant is that I was the dbo in the
original database and someone else is the dbo in the production
database. I still have administrator permissions, but I am not the dbo.
Would this have any effect on user security? I seem to be running out
of clues. Is there anything else I should check?

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ALTER PROCEDURE stp_SecGrantLoginDBAccess
@LoginName nvarchar(128)
AS

SET NOCOUNT ON

SET @LoginName = 'AD\' + @LoginName

EXEC dbo.sp_grantlogin @LoginName

EXEC dbo.sp_grantdbaccess @LoginName

EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'

SET NOCOUNT OFF

RETURN
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message And how did you achieve this the MSDE 2000 database? By following the
same settings, you should get the same result. My opinion is that you
have created SQL-Server User Accounts instead of Windows Accounts on
the SQL-Server. With Windows Accounts, the name of the machine should
be displayed; for example 'MyMachine\someuser' and not just 'someuser'.

It is also possible that these users doesn't have the right to
interactively logon on this machine. However, if you have used the same
machine as the MSDE 2000 database, then there should be no difference
there.

If you have used roles, then it's normal that individual permissions
remains unchecked for users. Only the permissions that you
specifically set for a user, independant of those granted by its
associated roles, will be displayed there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
now
the non-admin users are unable to connect to the production server.
The ADP
(Access 2002) application uses Windows authentication mode.

I can connect to the server just fine using the ADP file from any
desktop,
but other users are unable to connect from the *same* desktop
machines. The
error message is:

"[MyApp] could not log on to the server. Verify that the log on
information
is correct."

The File/Connection option does not display for these users, but when
I log
into the machine as myself, I can select this option. The connection
information is correct and I can connect to the server without any
problem
at all...probably because I'm an administrator on the server. The
only
other user who has been able to successfully connect to the server
using the
ADP is also an administrator on the server.

If another non-administrator opens the application while it is pointed
at
the test server (MSDE 2000), then the user can go into File/Connection
and
change the server to the production server. However, if the user
clicks on
the "Test Connection" button, the below error displays:

"Test connection failed because of an error in initializing provider.
Login
failed for user 'someuser'"

All the clues appear to indicate a security problem, but I can't seem
to
find it. When I go into Enterprise Manager on the production server,
the
users appear in the "Users" folder for the database. When I look

at

a