November 19, 2002 at 1:15 pm
I have been asked by a manager to create a table in SQL Server, and ensure that only she has access to this table. The table is meant to hold salary information for our department. I can obviously create a table and user to only give her access. However, since I am the DBA, I can always log on as SA to view this table. This is not acceptable.
From talking with some colleagues, I understand I could do some manual deletions to some system tables to not allow the SA user to view these tables. Of course, I don't really want to mess with the system tables as this is a production database.
Any ideas on how to deny access from SA, or a workaround?
November 19, 2002 at 1:27 pm
You can deny access to a table for dbo, which is the user for sa in a database. However, not sure dbo can own the table, so you might need another user to do this.
There is also encryption, but it's more complicated and not recommended.
Steve Jones
November 19, 2002 at 2:06 pm
thanks for the response Steve. I should have also noted that I am using 7.0.
November 19, 2002 at 2:46 pm
I think this will still work on 7.0. I reviewed a security book recently (not yet published) and the author had an interesting way to do this. Under NDA, so I can't describe it yet, but it would work. The facilities are there in 2000, I think 7.0, but I'm not sure.
Steve Jones
November 19, 2002 at 7:28 pm
First off SA is a member of the System Administrators role and has access to all things. You cannot use EM to drop it, however haven't tried and wouldn't but might could have the system tables to drop it from the role. I have in the past myself used triggers to prevent updates, deletes and inserts to the SA account as a test. The only other thing I could think of is to create a job to check sysprocess, regularly, and if SA is in the DB in question kill is connection but the person can just shutoff the job. Or build an outside app to do the same thing they cannot get to or stop. Other than that I would be interested in any other conceptual ways and may play with this to see if I can find one.
November 20, 2002 at 8:19 am
Thanks for the replies.
I did try to setup a user - "Salary User", and create a table with SalaryUser as the owner. I then went in (through EM) and explicitly denied access to DBO to the particular table. This didn't seem to stick. The next time I went in, all the check boxes were unchecked.
I agree with Antares686 that I don't want to modify the system tables to make this happen. Also, having sysprocess monitored doesn't really get us what we want, because, of course, malicious SA's could still break that monitoring.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply