I have a timeclock program that uses SQL Server Express 2005 and it runs
fine on Windows 2000. I installed SQL Server Express 2005 and my app on a XP
Pro box with no issues using a User account with Admin privileges.

When installing SSE 2005, I opted for Mixed Mode authentication because I
did not want Administrators to have access to the timeclock database unless
they specifically knew the SA account password. Unfortunately, it appears
that any user that is classed as an Administrator can access the database
with Windows Authentication mode. It's only when I change a user to a
Limited account that they are denied access if they use Windows
Authentication.

Is it possible to make authentication by using the SA login only? If so, how
do I change it? If not, is there anything else I can do to limit access?

Thanks,

Barry

Hello there!


Windows Authentication can not be disabled using Mixed Authentication. Mixed
Authentication means that you can use Windows and SQL Server Authentication
together. You can only disable SQL Server authentication and Microsoft
recommends using Windows Authentication when possible.

You can manage your Logins using SSMS and going to Security\Logins node.
Logins make your users to connect and perform their tasks in SQL Server.

By default, BUILTIN\Administrators and BUILTIN\Users logins will be created
in Logins. And BUILTIN\Administrators will have "sysadmin" server fixed role
which allows them to do whatever they want to do in your SQL Server
instance.

If you simply delete BUILTIN\Administrators login, then your local admins
will not be able to connect to your SQL Server instance using their Windows
Accounts.

CAUTION: Be careful when you are performing this action and ensure that you
have another account which has "sysadmin" server fixed role, otherwise you
may experience problems.

--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITPBA, MCSD.Net, MCSE, MCT



"BCS" <nospam@nowhere.com> wrote in message
news:471e3323$0$15380$4c368faf@roadrunner.com...
>I have a timeclock program that uses SQL Server Express 2005 and it runs
>fine on Windows 2000. I installed SQL Server Express 2005 and my app on a
>XP Pro box with no issues using a User account with Admin privileges.
>
> When installing SSE 2005, I opted for Mixed Mode authentication because I
> did not want Administrators to have access to the timeclock database
> unless they specifically knew the SA account password. Unfortunately, it
> appears that any user that is classed as an Administrator can access the
> database with Windows Authentication mode. It's only when I change a user
> to a Limited account that they are denied access if they use Windows
> Authentication.
>
> Is it possible to make authentication by using the SA login only? If so,
> how do I change it? If not, is there anything else I can do to limit
> access?
>
> Thanks,
>
> Barry
>

Thanks for the . That's exactly what I needed to know. Barry

"Ekrem Önsoy" <ekrem@btegitim.com> wrote in message
news:AEDB3330-C4B0-494C-B7C7-CEBF722557D1@microsoft.com...
> Hello there!
>
>
> Windows Authentication can not be disabled using Mixed Authentication.
> Mixed Authentication means that you can use Windows and SQL Server
> Authentication together. You can only disable SQL Server authentication
> and Microsoft recommends using Windows Authentication when possible.
>
> You can manage your Logins using SSMS and going to Security\Logins node.
> Logins make your users to connect and perform their tasks in SQL Server.
>
> By default, BUILTIN\Administrators and BUILTIN\Users logins will be
> created in Logins. And BUILTIN\Administrators will have "sysadmin" server
> fixed role which allows them to do whatever they want to do in your SQL
> Server instance.
>
> If you simply delete BUILTIN\Administrators login, then your local admins
> will not be able to connect to your SQL Server instance using their
> Windows Accounts.
>
> CAUTION: Be careful when you are performing this action and ensure that
> you have another account which has "sysadmin" server fixed role, otherwise
> you may experience problems.
>
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCTS: SQL Server 2005, MCITPBA, MCSD.Net, MCSE, MCT
>
>
>
> "BCS" <nospam@nowhere.com> wrote in message
> news:471e3323$0$15380$4c368faf@roadrunner.com...
>>I have a timeclock program that uses SQL Server Express 2005 and it runs
>>fine on Windows 2000. I installed SQL Server Express 2005 and my app on a
>>XP Pro box with no issues using a User account with Admin privileges.
>>
>> When installing SSE 2005, I opted for Mixed Mode authentication because I
>> did not want Administrators to have access to the timeclock database
>> unless they specifically knew the SA account password. Unfortunately, it
>> appears that any user that is classed as an Administrator can access the
>> database with Windows Authentication mode. It's only when I change a user
>> to a Limited account that they are denied access if they use Windows
>> Authentication.
>>
>> Is it possible to make authentication by using the SA login only? If so,
>> how do I change it? If not, is there anything else I can do to limit
>> access?
>>
>> Thanks,
>>
>> Barry
>>
>