DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     varchar(10)

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 varchar(10))  
INSERT into @table1 (col1) select name from sys.schemas where schema_id >4 and schema_id <22
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=col1
        FROM @table1
        WHERE RowID=@CurrentRow
    exec('grant ALTER,DELETE, EXECUTE, INSERT, REFERENCES, SELECT,UPDATE, VIEW DEFINITION on schema::'+@SelectCol1+' to <user1>   ');
    exec('grant select on schema::'+@SelectCol1+' to <user2>');

 

Background:

Sometimes there are SQL script operation against a list of values other than one, we need consider using variable to run one command on all of them other than run multiple commands. Below is an example.

To grant permission to <user1>and <user2> to 16 schemas.

select name from sys.schemas where schema_id >4 and schema_id <22 will get all the schemas, we assign them into a table, then use another command to run against each row of them.

Scripts:

 

About Lei

I am an IT specialist with over 10 year experience - years on Automation, on-Premise or Azure.

I am happy to develop however never want be a full time developer. Only do what I have to do. If it has to be PowerShell,HTML, PHP, CSS, C#, VBS or JS, front end or backend, so be it, doesn't matter!

Spent years with Windows, SCCM, SharePoint, SQL and Exchange servers. For last several years, I have been actively working under On Premise > Azure environment.

THERE IS NO WAY BACK!!!

Current Certificates:
    Microsoft® Certified-
  • -Enterprise Administrator
  • -Database Administrator
  • -SharePoint Administrator
  • -Administering and Deploying SCCM 2012
Red Hat Certified Technician
ITIL V3 Foundation - Practitioner

Working on Azure Certificates now and hopefully they can stop upgrading their questions one day! GIVE ME A BREAK!

Contact Lei

Name *
Email *
Comments *

Traffic since 10/08/2016

Today13
Yesterday83
This week637
This month1933
Total416293

Visitor Info

  • IP: 54.221.73.186
  • Browser: Unknown
  • Browser Version:
  • Operating System: Unknown

Who Is Online

1
Online

2017-12-17

Login