0

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.

1

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;

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.