Removing Access to Tables - but can still run queries on them??

[Antelope]

I've been charged with securing a SQL database after we found a security breach so I am a bit under the gun on this one.

There is a SQL database with a ton of tables, but only 2 of them contain employee data that I need to restrict. If I setup Windows Only Authentication, add my user as an authorized user on that database, go to properties on that table, and deny access to that table I can still go to Import/Export Data on a client machine and extract the information to a file. What am I doing wrong?

 

[JBaileys]

Are you an admistrator on the machine? By default an administrator on the machine has full admin rights on the db.

 

[Antelope]

ah yes, I just read that too. Since I am a domain admin, even though I am denying specific tables, it will still allow me access to them.

 

[TheBugSlayer]

Check the rights of the user that's connecting to your SQL Server from the clients machine. Try adding that user to the db_denydatareader and db_denydatawriter database roles.

 

[Antelope]

Won't that deny him access to the entire database? I want to allow access to the database, but deny access to the specific tables.

 

[SQLBill]

Admins have database sysadmin (SA account) privileges by way of BUILTIN/ADMINISTRATORS access. Remove that.

Now ALL admins will have to have their OWN login (unless they know the SA password).

By the way, make sure the SA account has a good, strong password.

-SQLBill

 

[JBaileys]

One option is to setup a user that and you set the permissions by selecting the table you want them to have rights too.

You can view this by selecting a new role...