rmechaber (9/10/2010)
Mike Hinds (9/9/2010)
What we finally did was:[font="Courier"]EXEC sp_msForEachTable 'DENY SELECT ON ? TO [TheUser]'
GO
GRANT SELECT ON [dbo].[AllowedTable] TO [TheUser]
GO[/font]
For anyone considering this, sp_msForEachTable is "non-supported".
But what happens when you add another table in the future and haven't removed the user from whatever group has given him access? The user will then have access to the new table, no?
Rich
You're exactly right, Rich. This was my big objection to this method. The database belongs to a 3rd party app, and tables will not change until the app gets an upgrade. The only thing is, the programmers who need this access expect me to remember to tell the engineer who is in charge of upgrades, that my script needs to be run AFTER the vendor's upgrade is complete.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS