Creating scripts for repetitive tasks

One of the most mundane task to do is providing permission to a user on multiple stored procedures in a database. There is not much you can do, either provide ddladmin permission onto the database (it provides much more permission than required) or manually provide permission to each SP using Grant Execute or using GUI.

Permission window

In such cases where you need to run the same script on multiple objects, you can use the Select and concatenate to create your script. In this case I’ll write a query like below. Github link.

#Use below to Grant execute permission to all the stored procedure to user test

#This is basicall know-how to use SELECT with concatenation to generate scripts

SELECT ob.name, * , 'GRANT EXECUTE ON ' + sc.name + '.' + ob.name + ' TO test' 
FROM sys.objects ob
JOIN sys.schemas sc
ON ob.schema_id = sc.schema_id
WHERE type_desc = 'SQL_STORED_PROCEDURE'

Similarly you can create scripts for repetitive tasks.

I hope it helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s