Friday, 21 May 2010

Programmatically grant user privileges

When installing services, in particular SQL Server, it is a frequent requirement to create a service user and assign certain rights. To assist users during installation it is helpful to gather the required information (e.g. username + password) amd programmatically create the user for them. To achieve this you will need to create the user, assign it to the appropriate groups and grant any specific rights. Granting privileges and impersonation is covered in Corinna John's CodeProject article: http://www.codeproject.com/KB/cs/lsadotnet.aspx
How to add a user to the local system using C#: http://support.microsoft.com/kb/306273
How to add a user to the domain using C#: http://en.csharp-online.net/User_Management_with_Active_Directory%E2%80%94Creating_Users

Compress and de-compress strings in C#

A collection of resources on compressing and encrypting strings in C#.
Microsoft documentation on the System.IO.Compression.GZipStream class:
http://msdn.microsoft.com/en-us/library/system.io.compression.gzipstream.aspx
Helpful example class utilising GZip:
http://www.csharphelp.com/2007/09/compress-and-decompress-strings-in-c/
The GZipStream class is predominantly used for file compression but can be used efficiently for compressing strings of 300-400 characters or more. Below 300 characters there isn’t any measurable gain from the compression and for particularly short strings the compressed version may in fact be larger.
Class provided by C Sharp Help:
using System.IO.Compression;
using System.Text;
using System.IO;

public static string Compress(string text)
{
    byte[] buffer = Encoding.UTF8.GetBytes(text);
    MemoryStream ms = new MemoryStream();
    using (GZipStream zip = new GZipStream(ms, CompressionMode.Compress, true))
    {
        zip.Write(buffer, 0, buffer.Length);
    }

    ms.Position = 0;
    MemoryStream outStream = new MemoryStream();

    byte[] compressed = new byte[ms.Length];
    ms.Read(compressed, 0, compressed.Length);

    byte[] gzBuffer = new byte[compressed.Length + 4];
    System.Buffer.BlockCopy(compressed, 0, gzBuffer, 4, compressed.Length);
    System.Buffer.BlockCopy(BitConverter.GetBytes(buffer.Length), 0, gzBuffer, 0, 4);
    return Convert.ToBase64String (gzBuffer);
}

public static string Decompress(string compressedText)
{
    byte[] gzBuffer = Convert.FromBase64String(compressedText);
    using (MemoryStream ms = new MemoryStream())
    {
        int msgLength = BitConverter.ToInt32(gzBuffer, 0);
        ms.Write(gzBuffer, 4, gzBuffer.Length – 4);

        byte[] buffer = new byte[msgLength];
        ms.Position = 0;
        using (GZipStream zip = new GZipStream(ms, CompressionMode.Decompress))
        {
            zip.Read(buffer, 0, buffer.Length);
        }

        return Encoding.UTF8.GetString(buffer);
    }
}

Friday, 7 May 2010

Drop Default Constraint

The basic syntax for dropping a constraint is quite simple:
ALTER TABLE {{MYTABLENAME}} DROP CONSTRAINT {{CONSTRAINTNAME}}
The problem comes if you have created unnamed constraints as SQL will have assigned a random name. You need to discover the constraint name before you can drop it.
To discover the constraint name:
SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = '{{MYTABLENAME}}' AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id('dbo.{{MYTABLENAME}}') AND name = '{{COLUMNNAME}}')
To discover and drop the constraint:
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000);
SET @defname = (SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = '{{MYTABLENAME}}' AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id('dbo.{{MYTABLENAME}}') AND name = '{{COLUMNNAME}}'));
SET @cmd = 'ALTER TABLE {{MYTABLENAME}} DROP CONSTRAINT ' + @defname;
EXEC(@cmd);
GO
alter table {{MYTABLENAME}} drop column {{COLUMNNAME}}
GO