Jan 21, 2014

How to delete all rows from all tables in a SQL Server database?

Although you can write a delete command for all tables of SQL Database separately. But consider if you're wishing to delete all the data from a large database. i.g; A database with more tables. And where you're not sure with the Tables Names and relationships of the tables. In simple meaning; you're not familiar with the database schema.

The easiest way is to write some small and simple SQL Script. Use sys.objects to fetch all the tables from the database. Prepare the Delete Command for each table using Cursor.
execute sp_executesql System Stored Procedure to execute the delete command.

This will remove the dependencies for that particular table (under execution) and delete all the records from the table. After the Scripts completed the database dependencies / relationship are remapped as previous.

Find the snapshot of SQL Script for deleting all the records / rows from all tables in a SQL Server Database.


----------------------

DECLARE @tblName NVARCHAR(MAX);
DECLARE @DeleteQuery NVARCHAR(MAX)

DECLARE curso CURSOR FAST_FORWARD 
FOR 
Select Object_name(object_id) AS tblName from sys.objects where type = 'U'

OPEN curso
FETCH NEXT FROM curso INTO @tblName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE @statement NVARCHAR(200);
SET @statement = 'DELETE FROM [' + @tblName+']';
PRINT @statement
execute sp_executesql @statement;
END
FETCH NEXT FROM curso INTO @tblName
END
CLOSE curso
DEALLOCATE curso

----------------------

You can write your own SQL Command for your needs. like Select / Update / Delete / Truncate etc...


Dec 23, 2011

Using UAC with C#

User Account Control (UAC) is a new technology introduced by Microsoft in Windows Vista and most of the time it is misunderstood by users and developers. It’s main purpose is to protect the operating system by running applications with reduced privileges.

Why should we use this? Most applications DO NOT require full privileges. Think to the applications you have written and ask yourself if most of the job can be done without full writes (if you write to disk think if you could write in the user’s folder or an isolated storage, if writing in registry to HKLM think if you could write to HKLU, etc). The answer is mostly sure “Yes”.

So why run applications with full privileges when they can be run with limited? Running with more privileges than required is just a security vulnerability - If an attacker exploits a vulnerability in your application he will gain more control.

There are two mistakes developers tend to do:unidentified_uac

  1. Request the end-user to run an application with full rights even though this is not necessarily (most of the time because of bad design practices)
  2. Do not request to user to run the application elevated but try to perform operations that require more rights

By design UAC can only elevate code at process level and only at process’ startup (means that a running process cannot be elevated). In the .NET world this also means that you cannot elevate code running in another app domain because the app domain is part of a running process. In order to elevate an existing application this must be closed and reopen with more privileges.

There are two types on UAC dialogs: blue and yellow. When you see a blue dialog you can be sure that the application requesting privileges is signed and trusted. The yellow dialog shows for any application that is not digitally signed and is not fully trusted.

User Account Control also prevents a lower privilege process to do the following (list below taken from MSDN):

  • Perform a window handle validation of higher process privilege.
  • SendMessage or PostMessage to higher privilege application windows. These Application Programming Interfaces (APIs) return success but silently drop the window message.
  • Use thread hooks to attach to a higher privilege process.
  • Use Journal hooks to monitor a higher privilege process.
  • Perform DLL injection to a higher privilege process.

Let’s see how an UAC aware application should look.

It should be composed of two executables (one that will be run with limited privileges and another one that will be started only with needed and with full rights) or two working modes (a mode for limited rights and another one for full rights). Either way you must remember that once you elevated the application and finalized the administrative tasks, the process should be destroyed in order to reduce an attacker’s privileges.

In order to launch an elevated process in Windows Vista the process must be started with the “runas” verb. The Verb property is part of System.Diagnostics.Process.StartInfo class. The code snippet that launches “notepad.exe” with full rights is showed below:

    ProcessStartInfo processInfo = new ProcessStartInfo();     processInfo.Verb = "runas";     processInfo.FileName = "notepad.exe";     Process.Start(processInfo);

If you choose to have only one executable file that acts differently based on permissions you should check if the user is part of the administrative group. In Vista even if your user is part of the Administrators group it runs with reduced privileges by default and gains his full rights on demand. The code below stores true in thehasAdministrativeRight boolean variable if the user’s privileges are administrative and false otherwise.

    WindowsPrincipal pricipal = new WindowsPrincipal(WindowsIdentity.GetCurrent());     bool hasAdministrativeRight = pricipal.IsInRole(WindowsBuiltInRole.Administrator);

To elevate the current application you must create a process with elevated rights and close the existing instance. However you cannot start a process with limited privileges – I couldn’t find a solution. Anyone knows how to start a less privilege process from a higher privilege one? The sample creates an elevated instance of the current executable and closes the existing one.

    RunElevated(Application.ExecutablePath);     this.Close();

RunElevated is a method that takes the name of an executable and spawns it in a new elevated process (see the attached code).

I have created a sample application that illustrates all the things written so far: it displays the user’s rights, elevates the current application and starts a process with more privileges. In order to see all features of the application you must have UAC enabled. You can download the code from this link.

Please note that here I recommend to run applications with limited privileges but there are situations when applications need to run unrestricted – this is the case of system configuration utilities or other special applications. What I want to say is that you should run applications in an unprivileged environment when possible.

This is part one of the tutorial. Part 2 will explain how to use the manifest file to specify that an executable must be always run with full privleges.

May 29, 2011

New Features Announced for Silverlight 5

Improved media support and rich UI capabilities

Hardware Decode and presentation of H.264 improve performance for lower-power devices to render high-definition video using GPU support.
TrickPlay allows video to be played at different speeds and supports fast-forward and rewind. At up to twice the speed, audio pitch correction allows users to watch videos while preserving a normal audio pitch.
Improved power awareness prevents the screen saver from being shown while watching video and allows the computer to sleep when video is not active.
Remote-control support allows users to control media playback.
Digital rights management advancements allow seamless switching between DRM media sources.


Building next-generation business applications


Silverlight 5 text (bottom) has improved clarity.

Fluid user interface enables smoother animation within the UI. Inter-Layout Transitions allow developers to specify animations to apply when elements are added, removed or re-ordered within a layout. This provides smoother user experiences when, for example, items are inserted into a list.

Text improvements make it possible to build rich magazine-style text layouts:

Multicolumn text and linked text container allow text to flow around other elements.
Tracking/leading set precisely how far apart each character is for full creative control.
Text clarity is improved with Pixel Snapping.
Text layout performance is significantly improved.
OpenType support has been enhanced.
Support for Postscript vector printing enables users to create reports and documents, including the ability to create a virtual print view different from what is shown on the screen.
Added support for double-click and Combobox type ahead.
Model View ViewModel (MVVM) and Databinding enhancements allow more work to be done more easily via XAML:

Debugging support now allows breakpoints to be set on a binding, so you can step through binding failures.
Implicit DataTemplates allow templates to be created across an application to support a particular type by default.
Ancestor RelativeSource allows, for example, a DataTemplate to bind to a property on the control that contains it.
Binding in style setters allows bindings to be used within styles to reference other properties.
The DataContextChanged event is being introduced. Markup extensions allow code to be run at XAML parse time for both properties and event handlers, enabling cutting-edge MVVM support.


Silverlight 5 performance improvements

Reduced network latency by using a background thread for networking.
XAML parser improvements that speed up startup and runtime performance.
Support for 64-bit operating systems.
Reduced network latency by using a background thread for networking.

Graphics improvements

Graphics Processing Unit (GPU) accelerated 3-D application programming interface (API) provides rich graphics on the Web for building advanced data visualizations and rich user experience (UI).
Immediate mode graphics API allows direct rendering to the GPU.
Hardware acceleration is enabled in windowless mode with Internet Explorer 9.

Silverlight 5 extends features of the "Trusted Application" model

Silverlight 5 extends features of the ‘Trusted Application’ model to the browser for the first time. These features, when enabled via a group policy registry key and an application certificate, mean users won’t need to leave the browser to perform complex tasks:

Host HTML content as a Web browser control within the Silverlight application. HTML pages, such as help content or e-mail, can be integrated within the application.
Read and write files to the user’s My Documents folder, making it easier to find media files or create local copies of reports.
Launch Microsoft Office and other desktop programs. Users can open Microsoft Outlook and create an e-mail message, or send a report to Word utilizing the power of Office.
Access devices and other system capabilities by calling into application COM components. Users can access a USB security card reader or a bar-code scanner.
Enjoy full keyboard support in full screen, which enables richer kiosk and media viewing applications.
Call existing unmanaged code directly from within Silverlight with PInvoke.
Tools improvements

Microsoft Visual Studio profiling support including CPU, memory, thread contention.
Visual Studio Team Test support.





Features included in the final release are dependent on feedback and testing that comes from close communications with partners and customers that participate in the beta process