May 16, 2006 at 7:10 am
Hi,
I have some simple question on SQL server 2000.
01) Why is it that when I detach a DB, change the SA password, then attach back the DB, I can still view the attached DB content. Should it be asking me for the old password before it can view the content ? Any way to implement such that the content cannot be view at all ?
02) If I want to protect my DB from user looking at my table structure and SP (stored procedure and views), any way or method to implement it ? I know that encryption of SP will not help much as there are third party tool to decrypt it back.
03) I just want to prevent unauthorized user from copying back my DB. Is encryption of the whole DB the only way, any other better way or method. Pleae advise.
Thank you
May 17, 2006 at 11:56 am
Simple questions do not always result in simple answers...
1) Logins, not users, have passwords. Logins allow access to the server not to individual db's. Access to db's is via users within the db's, aliases within the db or via membership in server roles. A login's password can be changed without affecting the database user's ability to access db objects (similar to changing your network password but still having access to resources on the network). 'sa' is a member of the sysadmin server role and by virtue of this membership can see & do anything on the server.
2) a. The easiest way I can think to do this is to create a database role (e.g. DDLDenied) in the db in question. Explicitly deny SELECT permissions to the sysobjects and syscomments tables for this role. Add the user(s) to this role.
b. Another, more restrictive, method (NOT TESTED!)... Script the permissions for the public role and alter the script so that "GRANT" is replaced with "DENY" and "public" is replaced with "DDLDenied". Be sure to use a 'clean' public role to generate the script in case permissions to user tables have been granted via this role. Execute the script in the database in question and add the user(s) to this new role.
c. The most restrictive method may be overkill (and HAS NOT BEEN TESTED). In addition to step b above, create a similar role in the master database but script the permissions for the public role in the master db, change GRANT to DENY and public to the new role name. Then add the public role as a member of the new role. This method has the potential to 'break' lots of things but should be easily reversible by removing the public role from the new (DDLDenied) role.
Once again, methods "b" and "c" have NOT been tested. Be sure to script the existing permissions on the role(s) before attempting anything! I would even go so far as to test this on a system that can be rebuilt if necessary.
Method "a" was cursorily tested and found to prevent common mechanisms of viewing DDL while allowing querying to occur.
You might also search the web for terms like 'locking down', 'preventing DDL access', etc. Include "SQL 2000" in the search terms as SQL2K5 has DDL triggers which address different but similar scenarios.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply