Sql-server – Implement Database Security

1. Windows Authentication should be chosen over SQL Server Auth. Windows Auth is inherently more secure, as it's controlled by the OS. Granted, there are times when you won't be able to get around a SQL Server Auth principal because of a third party application, or whatever the surrounding circumstances are.
2. You should have the amount of respective logins for each logical principal that can connect or could be audited. In other words, if every application uses the same login, and your auditing captures login name, it'll be virtually useless.
3. You should disable the sa account as it can be a security threat. The reason behind this is because it is extremely well-known login name for SQL Server instances. It takes out a lot of the guessing game for hackers. Disable this account, but ensure there are other appropriate principals with sysadmin privileges.

As for other security measures, they are far and vast. Our own member, mrdenny, has written a book on this very subject. A few pointers are you'll want to keep a tight sand box on your principals. In other words, give only permissions that the principal will need in order to perform the functionality, and no more.

Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.