SQL Server Performance Forum – Threads Archive

RE:MsSQL Security Issue

Hi, I having an issue on MsSQL Security Issue. Wish you guys can help me.
My problem is now I have create a login user and password for each Database. The problem is if the database file (.MDF, .ldf) has been copy out..user do attach manually, then put any new user and password to that db..so all my data will be view by others..is there any solution for this?
Another Question is for each DB..can we kick out sa user and put in our new user for that DB? So sa no permission on view/update/Add in records for that db? Thanks you.

First of all ensure to set complex password to SA account, which cannot be dropped or degraded in SQL server. Use ROLES to maintain security and access in the database, and take help of the following links to tighten the security : http://vyaskn.tripod.com/sql_server_security_best_practices.htm&e=7415 – Security best practices
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm&e=7415 – Row level security HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forums This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Moreover, the .MDF & .LDF files cannot be copied easily until unless SQL Services are stopped for some reason and ensure no OS user account has ADMIN privileges on the box. Review the information from thishttp://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp link. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forums This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

1) You can’t (and shouldn’t) ‘kick out’ the SA user from the DB. The SA user actually maps to the Administrator acoount but is used for SQL Server (Mixed Mode) authentication.
2)NTFS permissions can still be used to prevent copying/moving the DB even in the unlikely event that the SQL server service is stopped by someone other the Server Administrator (or sa in this case).
3)Just copying and attaching the DB across to another box will not necessarily give the ‘burglar’ permissions/rights on the database unless they are operating in your domain and you have given them Admin rights on their machine and/or the database in question. All the database users (SQL users) will be orphaned (different SIDs) except for Domain Accounts (same SID everywhere they logon on).
4)If you insist on using the sa account, provide strong authentication for it as Satya notes above.
5) Avoid giving users the ‘Logon locally’ right on your SQL Server box. That way they will not be able to try and stop the MSSQLServer service or do other stuff on your server. I don’t think there is a way to stop the service remotely (as far as I know).
6) Just follow the standard security guidelines provided by Satya’s links and I don’t think you will any reason to worry.
Nathan H.O.
Moderator
SQL-Server-Performance.com

Thanks you for reply.<br />I having this problem is because our database which is using MSDE will be install at All Clients PC. how ever, the owner of the database don’t want the data been view thur back door, only view by our application..<br />So we need to block all the data which can be view by clients…if the user know the ways of attach db..then no more security of our database..tat is what i concern..i know we can block the user to access the specific folder in server, but we not the owner of the client pc..they are the most powerful user for their own PC.. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /><br />-

Then you may choose to use APPLICATION ROLES and remove permissions to the PUBLIC role too in the database. Also on OS part make sure ADMIN privileged accounts are minimum and follow the rules framed in the MS link above. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Unfortunately if you distribute the actual files to the client PC and the user is admin of their PC, then there is very little you can do to protect the data, after all you’ve already published it to the client… You can make the security of the database tighter by using application roles, but at the end of the day, they can install another SQL instance, copy the database files (since they are admins) and since they are sa on the new instance, they can do whatever they like there… Sorry to be the bearer of bad news… Cheers
Twan

]]>