SQL users (DMO)

SQL 2000 provides two security models; SQL and Windows. To use Windows you need to create SQL logons and users to match the Windows user name. This requires the use of SQLDMO. Whilst SQLDMO is very powerful it isn't a core part of .NET and so there are some quite odd features - especially in searching some of the collections. We seem to end up simply iterating through the collection to find what we need.

Secondly adding/removing users and logons whilst appearing straightforward has a number of catches - such as - do they own the database? Does a logon already exist? What if they have access to other databases - how do you delete them without breaking that access?

Suffice to say what should be a short function ends up much longer with a number of checks and balances - but once cracked it allows an application to add/remove users and therefore access to it's database - without having to involve the IT people every time a user change is required.