How to Password Protect my SQL Server Database?

Question

I want to deploy my SQL Server 2008 Database with application on the client's machine, where i don't want that anybody can open and read or copy my Database Objects (i.e. Tables, SPs, Functions, Views etc.). I want to protect database with password same like Microsoft Access 2007 When somebody opens it asks Password. Can it be done in SQL Server 2008, if yes how.?

Answer 1

SQL Server is not like Access and doesn't have any file-based password-protection mechanisms (in the "normal" editions like Web, Standard, Enterprise/Developer, Express).

SQL Server is a server-based product - you need to restrict physical access to the server the files are located on, and prevent unauthorized users from accessing the files through the SQL Server mechanisms (like logins and permissions).

If you need something like Access' password-protected files, you need to use SQL Server Compact Edition which is file-based and has a password-protect feature