Any security check to stop Importing Data from SQL tables into EXCEL

[BSRK]

I am storing some user IDs & passwords in a table in SQL server. Users will login into various database related applications & they can update the data there. Also, they can change their respective passwords also. But anybody can goto EXCEL and from Data (Menu) -> Import External Data -> and New Database query, and get all the information into the excel sheet. Is there any method to stop this?

[Steven Campbell]

There are good security practises that you can use to make sure that sensitive data (passwords, credit card numbers) etc are either encrypted or hashed in the database, so that a query cannot be used to view the data. See this article for an example of this technique.

---

my blog

[BSRK]

Dear Sir, Thank you very much for the answer and also a very good & useful article. Though I am happy with the suggested method, I would also like to know whether "Is there any way to stop this Importing Data in to EXCEL" Thanking You

[Mike Dimmick]

Yes. Make the users log in to SQL Server using an unprivileged login - e.g. one that is a member of the public role but not a member of dbo. This is a best practice in any case. Do your password checking in a stored procedure. Grant EXEC permission for public to the stored procedure, and deny SELECT permission to the table containing the usernames and passwords. Don't ever give out your sa password. Make it a strong one. SQL Server's security features are pretty powerful but you have to use them. Far too many applications use sa to connect to the database. Applications and users should always be limited to only seeing the data and performing the operations you specify. Stability. What an interesting concept. -- Chris Maunder