Select permission to each table

I have three schemas Production, HRecourse and Sales,tables in every week create and drop , I have six Users P_User1, P_User2, HR_User1, HR_User2 and S_User1 and S_User2. I have to grant then how i can Select permission to each table in their respective Schemas. how i tackle this scenario with SQL Scripts.

nstead of dropping and re-creating, why not just keep the same tables and truncate them and re-populate them? Then you don’t have to mess with permissions each week.

However, if you want to keep doing this the hard way, this script might help.

SQL Server 2008+:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH [schemas] AS 
(
  SELECT id = [schema_id], name
  FROM sys.schemas 
  WHERE name IN (N'Production',N'HResource',N'Sales')
),
[users] AS 
(
  SELECT u, [schema] = s FROM 
  (
    VALUES 
    (N'HR_User1',N'HResource'),
    (N'HR_User2',N'HResource'),
    (N'P_User1', N'Production'),
    (N'P_User2', N'Production'),
    (N'S_User1', N'Sales'),
    (N'S_User2', N'Sales')
  ) AS v(u,s)
)
SELECT @sql += N'
GRANT SELECT, UPDATE, INSERT ON '
  + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' TO ' + QUOTENAME(u.u) + ';'
FROM [schemas] AS s 
INNER JOIN [users] AS u
ON s.name = u.[schema]
INNER JOIN sys.tables AS t
ON s.id = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

If you’re stuck on 2005, you’ll need to make a couple of changes (and start thinking about your next move):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

;WITH [schemas] AS 
(
  SELECT id = [schema_id], name
  FROM sys.schemas 
  WHERE name IN (N'Production',N'HResource',N'Sales')
),
[users] AS 
(
  SELECT [user]    = N'HR_User1', [schema] = N'HResource'
    UNION ALL SELECT N'HR_User2',            N'HResource'
    UNION ALL SELECT N'P_User1',             N'Production'
    UNION ALL SELECT N'P_User2',             N'Production'
    UNION ALL SELECT N'S_User1',             N'Sales'
    UNION ALL SELECT N'S_User2',             N'Sales'
)
SELECT @sql = @sql + N'
GRANT SELECT, UPDATE, INSERT ON '
  + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) 
  + ' TO ' + QUOTENAME(u.u) + ';'
FROM [schemas] AS s 
INNER JOIN [users] AS u
ON s.name = u.[schema]
INNER JOIN sys.tables AS t
ON s.id = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;