In: Uncategorized
Written by: Taylor Gerring
On this day of voluntary internet blackout to protest PIPA/SOPA legislation, I felt compelled to write my representatives and share those thoughts with everyone, to reuse freely without permission. In other words, I’m offering up the following text for public domain. Do with it what you please:
Dear Representative,
As you’re undoubtedly aware, today many websites across the internet are taking up a “Stop SOPA” campaign in which many voluntarily refuse access to visitors to simulate the results of passing of PIPA/SOPA in its current form.
As a Software Engineer by trade, and writer & photographer in hobby, I understand and appreciate the need for copyright laws. However, as a citizen of Chicago, Illinois, United States of America, I also understand that the copyright law now represents the interests of mega-corporations instead of the common man.
Not only do I support the strikedown of SOPA-like legislation, so too do I support the reversal of the copyright extensions that companies have long lobbied for. It is with great irony that content producers who use public domain works to produce a derivative work (such as classical music in old Disney cartoons) work to battle the realization of old works entering the public domain after a sufficient length of copyright.
How better can an American contribute to the general welfare of society than contribute their intellectual property when they no longer can benefit from it? To place untenable limits on such actions is to legislate against 313 million Americans, just as passing SOPA to allow the take-down of whole internet sites without notice or due process is unfathomable.
Stop SOPA. Repeal unreasonable copyright extension.
Sincerely,
Taylor Gerring

To the extent possible under law, Taylor Gerring has waived all copyright and related or neighboring rights to Stop SOPA.
This work is published from: United States.
In: SSIS
Written by: Taylor Gerring
In the world of ETL, it’s quite common to need to know the current date, especially without the time component. Because Script Tasks allows us to code up anything in .NET, it should be fairly obvious how to generate and expose the current date. for example:
Dts.Variables["TheCurrentDate"].Value = DateTime.Date;
That little snippet of code will store the current date into a variable named “TheCurrentDate”. This is straightforward and there’s absolutely nothing wrong with the approach, however, there’s a simpler way to fetch this information without the need of adding a whole control flow item for such a minute amount of work. It’s brilliantly simple, only requires the creation of a variable, and can be added in about 10 seconds:
Read the rest of this entry »
In: SSIS
Written by: Taylor Gerring
This isn’t my first adventure in connecting to Oracle from SQL Server. No, in fact, I wrote a guide on that very topic nearly 3 years ago. Unfortunately—as a technologist, but fortunately as a blogger—the times change as does the method for setting up the configuration. On the plus side, I’m much more confident in the setup and am convinced that it’s easier than ever to connect SSIS to Oracle. Without further adieu, let’s get started by downloading and installing a couple of necessary components:
Read the rest of this entry »
In: SQL Server
Written by: Taylor Gerring
As a followup to my post on SQL Server Principals & Roles Permission Audit, it’s completely expected that you may want to act on the results of that audit. Specifically, let’s say you want to organize users into groups and enforce group permissions across the instance. In a particular scenario at my company, we are constantly refreshing databases down from production, which has a nasty side-effect of destroying the finely-tuned permissions we’ve carefully set in place. What made the most sense to reduce the friction this would cause was to reset the permissions via a job. This allows us to schedule it nightly, fixing any broken permissions, but also makes it easy to fire on demand, should needs dictate. Of course, it would be easiest if this were all contained in a stored procedure, but how do we juggle all these users and groups when you’re hosting 50 databases?
What made sense for us was to be able to supply the name of a principal, a “like name” for the database, and the role that they should have. What the “like name” allows us to do is apply a permission to a series of databases based on name. For example: ideaexcursion1, ideaexcursion2, ideaexcursion3 can all be set by supplying the paramater ideaexcursion%. This increases the complexity by forcing us to use a cursor in the proc, but reduces the number of actions we need to manage.
exec dbo.pr_SetGroupPermissions @principal = 'domain\principalname' , @db_likename 'ideaexcursion%' , @addrole 'db_owner'
In: SQL Server
Written by: Taylor Gerring
Thanks to the expansion of catalog views in SQL Server 2005, managing users and permissions has become much easier. Specifically, if you largely manage users by way of roles, rather than per-object permissions, SQL exposes this relationship in a single view, which can produce a report of what principal is part of what role. Check out the query:
select db_name() [database_name] , dbpm.name, dbpm.type_desc, dbpm.create_date, dbpm.modify_date , dbpr.name, dbpr.type_desc from sys.database_role_members dbrm left join sys.database_principals dbpm on dbrm.member_principal_id = dbpm.principal_id left join sys.database_principals dbpr on dbrm.role_principal_id = dbpr.principal_id order by dbpm.name, dbpr.name
This is handily fantastic and works well when you’re just managing access to a single database. What about if you want to audit logins for the entirety of the database instance? Unfortunately, we have to rely on database cursors to build a query and execute it. The concept is easy, but the details can be a little tricky to manage. Here’s what I came up with:
Read the rest of this entry »
