Tuesday, August 12, 2014

Step types: List

Hello!

This is the last step type I will be talking about in this series of posts about the package step types in TransSQL. In practice, a List step type can not be added to a package. Actually if an application administrator unchecks the "Package entry: Let users run SELECT queries against source and target servers." option in the Settings page, then users do not see this step type in the user interface at all. I will be talking about all the available Settings later, in another post.

The aim of the List step type is to let the users to run SELECT queries against the source and target tables. By deploying your SQL statements using TransSQL, you already eliminate the necessity to grant modification permissions to your users. Using the List step type, you do not need to grant even SELECT permissions to your users. All is handled by TransSQL!

Technically, it is not possible and safe to prevent users from running some other commands than SELECT in a screen like this, at least we have not figured it out yet! So we preferred using another application user account to run SELECT statements and this account has only SELECT permissions. This account is created automatically by TransSQL. So there's no any potential breach problem using this method. So, as you can figure it out, there are two application users of TransSQL. One is used for deployments and the other one is used to run SELECT statements against your source and target database tables.

Step type: List

Above, you can see a screen image of List step type page. You can choose the source or target database from the Environment drop down box in the Step Details group box. Just choose your environment, write your SELECT statement and hit F5 as you do in SQL Server Management Studio. An application administrator can limit the result set for the target environments as the data at the target tables may be critical. For instance in some scenarios, some third party auditors do not want your users to query production tables in large chunks. An application administrator can also prevent the result set to be copied to clipboard. All of these options are available in the Settings tab. Below, you can see a partial screen image from the Settings tab which is related to List step type:

Some options related to List step type
For your comfort, each row is numbered starting from 1 and total row count is shown at the left bottom corner of the window.

You can also transfer a selected text or all text (if not selected) to the Query step type window using the small buttons at the left side of the statement text box. It's also possible to save the result set into a *.csv file, again using one of those small buttons at the left or using the context menu of the data-grid using the right mouse button.

Thanks for following up to this point. This post concludes our step type blog series.
Ekrem Onsoy

No comments:

Post a Comment