steveo1967 (10/10/2012)
Thank you all for the excellent replies. I'm still trying to work out our security issues. I set up the SQL server with Windows authentication. I set it up so that my domain account would be SA. It looks like anyone who has access to the domain (not just local users) are able to access the database using SQL Management Studio. What really has my head spinning is that if I remove the drive on which our database is installed from my domain computer and attach it via USB enclosure to my laptop which is OFF the domain and also running SQL Server 2005 Express, I can attach the database to my SQL server without requiring ANY authentication. Is it because authentication pertains to SQL Management Studio and not the database themselves? Thanks again for your time, I really appreciate it.:crazy:
Authentication occurs at the instance-level. Authorization occurs at the database-level. This is why you can take a database's data and log files from one instance, attach them to another instance and members of the sysadmin Role on the new instance will have full control over the database.
You can protect data and log files at rest using TDE (transparent data encryption) but that is only available in Enterprise Edition 2008 and above.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato