CodeSmith Template to Grant Execute Permission for a User to all Stored Procedures

I like to use CodeSmith to generate a lot of my common code. I hope to make my templates more efficient in the coming months and share some of that with you as it happens. But last week, well late Friday afternoon I was presented with a challenge at a client. Their DBA had decided to remove execute permission from the user account for all stored procedures in the QA database! I was then told, oops sorry you will have manually go back and add that permission since there are a lot of stored procedures.

Well at first I was pretty bummed (this is a family oriented Blog so fill in any other personal favorite for bummed). But I decided to make some lemonade and share it with everyone today. I knew CodeSmith could roll through the list of stored procedures and provide me the name of each one. So I decided instead of looking for a standard SQL script or other main stream solution to write my own way. My reasoning, it has to be a pretty quick job for CodeSmith to perform and it should be a very simple CodeSmith template, meaning not a lot time to invest.

Here is the full CodeSmith template:

<%@ CodeTemplate Language="VB" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>

<%@ Property Name="DBSource" Type="SchemaExplorer.DatabaseSchema" Category="Data" Description="Command to build method from." %>
<%@ Property Name="UserName" Type="System.String" Default="PZXW_admin" Category="Data" Description="The Username to be granted permission" %>

<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>

<% For Each c as CommandSchema in DBSource.Commands %>
grant exec on <%= c.FullName%> TO <%=UserName%>
<% Next %>

First I declare two properties, DBSource and UserName. The DBSource is a DatabaseSchema and CodeSmith will automatically provide us with an easy interface to select which database to use by clicking the ellipsis button in the Properties windows for the property. The DBSource will be used to loop through all the Commands or stored procedures. The UserName is a String property of the account to be granted execute permission.

Next I add and import the necessary assembly and namespace. For this script we need the SchemaExplorer namespace.

Finally the real work, looping through the stored procedures. The DataBaseSchema object has a Commands property. The Commands property is a CommandSchemaCollection, which as you can assume is a collection of CommandSchemas. Each CommandSchema represents the details of a stored procedure. In this case the FullName property of the CommandSchema returns the full proper name, dbo.spGetItem for example. This is all we really need to call the grant function. So we grant execute permission, exec, to the stored procedure to the provided UserName. The loop will take care of listing one store procedure on each line, then copy this to your favorite SQL editor and execute! This worked great for me and took me about 5 minutes to write. Saved me hours of mindless coding or checking of items in a small dialog box.

Share This Article With Your Friends!