This months TSQL Tuesday is hosted by a good friend Pat right over at  SQL Asylum

For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.

The Meta Script

In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something done that would otherwise be a mundane repetitive task, this can save a world of time.

Lets say we have a list of objects in the Sales Schema and we have a request to grant Select and Insert access to a user for those objects.  There are two approaches, 1 is to grant select and insert to the actual schema like this

GRANT SELECT, INSERT ON SCHEMA::Sales TO BusinessUser 

However you might decide that you only want to grant direct SELECT and INSERT on the tables that exist in the DBO Schema today not those tables which may be created in the future (auditors love to make us do this)

A simple way to automate granting these rights is by writing a script that writes a script like so

SELECT
‘GRANT SELECT, INSERT ON ‘ + sch.name + ‘.’ + obj.name + ‘ TO BusinessUser’
   FROM sys.all_objects obj JOIN sys.schemas sch
 

    ON obj.schema_id = sch.schema_id

  WHERE sch.name = ‘Sales’
  and obj.type = ‘U’



This should give you a result set that looks something like the following:

GRANT SELECT, INSERT ON Sales.People TO BusinessUser
GRANT SELECT, INSERT ON Sales.Sales TO BusinessUser

At this point, run the output in a separate command window and viola you’ve automated that grant of permissions

This may not be true “automation” in the sense that Pat was looking for but, perfecting the ability to write scripts that write scripts is a huge timesaver