Re: ADP/SQL Server 2000 Security Problem

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.


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eVsoQh3PFHA.3596@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> "MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:N9G6e.1915$dT4.1516@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > 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.
> >
> >
> > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> > wrote in message news:%23GE0wDwPFHA.1500@xxxxxxxxxxxxxxxxxxxxxxx
> >> 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
> >>
> >>
> >> "MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:i1F6e.603$J12.399@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >>> 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?
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>


.