You've explained that you don't have the SA password and you're not an administrator. In that case, you're going to have to hack your way around it.
See Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again. by Argenis Fernandez.
That post explains how to impersonate the NT AUTHORITY\SYSTEM login using PsExec (or creating a Windows Scheduled Task running under the SYSTEM account) to create a new login for yourself. Then, you can log in via that new login, and grant your Windows account SA permissions.
I take it back! Just stop using the Template Explorer. You can edit the files here:
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\
Common7\IDE\SqlWorkbenchProjectItems\Sql\
For example if you edit the file:
Stored Procedure\Create Stored Procedure (New Menu).sql
Then right-click Stored Procedures and select New... and you will see the changes you made. I'm not sure why the Template Explorer exposes that item to you when it's not the one used in the menu. I couldn't figure out why making changes to the template didn't reflect what I was getting when I right-clicked in Object Explorer.
I'll leave the rest of the answer here for posterity. Especially the part about SQL Server 2012, since it handles snippets in a much more intuitive and useful way.
I don'tdidn't believe you cancould interfere with what Management Studio adds to the template when you open them... as far as I cancould tell that comment is injected internally.
But I have a better recommendation anyway: use SQL Server 2012 Management Studio and its new Snippets feature. Some benefits over templates:
- You can store snippets in a network location, and all of your users can point their custom snippets folder to the same place. This means that if you want to change the template for your stored procedure, let's say, you change it in one place and everyone's next use is up to date - you don't have to distribute an updated template to every single user.
- No hokey VB6-looking template parameters dialog. You can just use tab to cycle through and replace tokens in the snippet. You should try it out, I can almost guarantee you'll like it better. (Plus you'll get a bunch of other enhancements, such as IntelliSense that actually starts to live up to its name.)
Back in January, I wrote a detailed tip about setting up snippets:
http://www.mssqltips.com/sqlservertip/2589/use-sql-server-code-snippets-to-encourage-consistent-conventions/
Best Answer
No way to do this as part of a script from SSMS, but you do have two options.
One thing you can do is use SQLCMD mode and the ::connect command in order to have a script that will connect to multiple servers and run the script. This works well if you save the script for the user and use the :r command to load the script from a file.
Another thing you can do is configure a Central Management Server and then run your script against multiple servers at once.