1

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?

1

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.