2

I have a login I've created on SQL Server 2008, it's a SQL Server login not using AD. I want to give them access to every table in two databases except the tables that I store login information in for sites and such. This is one table in each database. Ideally, I'd like them to automatically get access to any new tables added to either database without breaking that restriction on those two login info tables.

4
  • 2
    Create a separate schema for the login tables like login, then deny the user(s)/groups(s) access to do anything on the login schema. This solution will scale, if you end up have to added more tables to the login schema.
    – jkdba
    Mar 11, 2016 at 16:44
  • 1
    Why the down votes?
    – jkdba
    Mar 11, 2016 at 16:45
  • 1
    Thanks! Yeah, no idea on the downvotes, I guess some people don't like people asking for help.
    – Rich
    Mar 11, 2016 at 17:05
  • When you say "get access" are looking at allowing them CRUD access or full control of the table to modify the table schema?
    – Aaron
    Mar 12, 2016 at 19:55

1 Answer 1

1

Just place a DENY ALL on the table for that user. DENYs are always evaluated first, and will override the GRANT off SELECT to the overall schema:

DENY ALL ON [schema].[object] TO {user_name}

There is no reason to move the login table into a separate schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.