Re: ADP/SQL Server 2000 Security Problem

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 news:OH7pH%23uPFHA.2460@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> "MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:vVC6e.553$J12.287@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>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?
>>
>>
>>
>
>


.

Loading