I want to grant a user access only to specific tables and I want to do it with least administrative effort. All tables are located in a database call 'Reporting'. And I only want the user to access tables which contain the word 'Finance'. Any idea how I could do it with least administrative effort?
So this assumes that you have already created the user on the server/database but a simple script should help
USE [Database] GO SELECT 'GRANT SELECT ON [' + schema_name(schema_id) + '].[' + name + '] TO [InsertUser]' FROM sys.tables WHERE name LIKE '%something%';
and run the results in a new query window. You could probably create a stored proc to do it as well to save for the future and just pass in the user and table name