I know we have spoken about database exceptions in the past, but there is really only one way to go.

The procedures must not catch any database exceptions.
This is very important because catching exceptions in the procedures and returning a recordset violates current standard best practices.

I have often been seduced to give it a try, permitting the procedures return some value on error/exception, and some other value on success.
There may be potential in this technique, but every time I try it things become terrible and I am reminded why current standard best practices require otherwise.
Exception handling becomes impossible, and the quality of the applications using the database is ruined. Things become very brittle and difficult to maintain.
The application will ensure everything is wrapped in a transaction and perform its own commit's and rollback's in response to any exceptions thrown by the database server,
so don't worry about using the try/catch blocks for those reasons.

First of all, the Sql login MdaWebUser will need permission to each and every stored procedure. This is the login used by the system itself. If there are any dependencies (such as access to the system's encryption keys) which require explicit permissions then these too shall also need to be granted.

Before we can get into much of the business logic and changes to the business procedures you raised yesterday, I have been redirected back to the original flow discussed some weeks ago which begins with the user login. You presently have some procedures for this, but these will require some changes as I detailed in previous email.

-- Validating a user's login (the ``login procedure'') --
You presently have something close to these lines (common.usp_Password_Check), which will need some alteration to meet our framework requirements, and some business rules will need to be present. I will need this done as soon as possible, given the login procedure is the very first thing we'll need done. This procedure is invoked by the system, not the user, and so there is really no ``user context'' to speak of.

Firstly, the procedure should return 0 on failure condition, and 1 on success condition.
Secondly the procedure should accept a number of input parameters. The username we are attempting to validate (@username), the password we are attempting to validate (@password), a boolean indicating if we are to update the field indicating the last dateTime the user logged in (@updateLastLogin), a boolean indicating if we are to update the field indicating the last dateTime of the user's activity (@updateLastActivity), a boolean indicating if we are enforcing the user's ``IsApproved'' property (@failIfNotApproved), the maximum number of failed attempts (@maxPasswordAttempts) the user is permitted within a specified window in minutes (@passwordAttemptWindow).

If @username is not found then return 0
If the user is lockedout then return 0
If @failIfNotApproved is true, and the user's approved status is false, return 0
If the @password matches {
If @updateLastLogin is true then insert (@username, GetDate()) into the ``user login success'' table
If @updateLastActivity is true then set the user's lastActivity property to GetDate()
return 1
}
Insert into the ``user login fail'' table (@username, GetDate())
(the following is expressed in pseudo sql as well, to assist with understanding)
If the count of failed logins within the window parameter meets or exceeds the specified attempts {
Set the user's IsLockedOut property to true
Set the user's LastLockedOut property to GetDate()
}
Return 0

--Peudo sql which might prove helpful for the above comparator
If ( @maxPasswordAttemptWindow <= ( select COUNT( * )
from ``user login fail table''
where (
( Username = @username )
and ( DATEADD( minute, -@passwordAttemptWindow, GETDATE() ) <= [When] )
) )
) begin
Update ``user table'' set LastLockedOutDateTime = GetDate(), IsLockedOut = 1 where Username = @Username
End
Return 1

--Retrieving a user Login--
I need a GetMembershipUser (I don't care what it is actually named) procedure which only a few parameters. We will need the username we wish to retrieve (@username), and a boolean indicating if the system should update their last activity datetime (@updateLastActivity).
The data should be returned as SELECT output. The procedure should return only ZERO or ONE recordsets (which may be empty if the specified user does not exist in the system). If the @updateLastActivity is true, then the system should set the LastActivityDateTime to GetDate() before returning. The returned select statement should return the following:
The username, as found in the database. NOT NULL (we will return an empty recordset in this case)
The email address associated with this user.
The user's password recovery question.
The comment field for this user.
The IsApproved and IsLockedOut fields. NOT NULL (I would default IsApproved to 1 and IsLockedOut to 0)
The DateTime of this user's Creation, LastLogin, LastActivity, LastPasswordChanged, and LastLockout. NOT NULL (they should all be set to the Creation field's value when the record is added, per the future CreateUser procedure)
Because this query might conceivably be invoked by an end-user as well as the system, a parameter indicating exactly who is requesting could be included (@requestor).
The logic is fairly straight-forward:

If ( ( @username == @requestor ) or ( requestorHasRightsToRunThis ( @requestor ) ) {
If ( @updateLastActivity ) then set LastActivityDateTime = GetDate() where Username == @username
Select top 1 username, email, ... where Username == @username
}
return

I am not entertaining any user management procedures at this time. We need to get this much done, and quickly. Bob was quite explicit this morning, and the first step was login.

It was wonderful to meet you yesterday. Now with the polite banter out of the way, let's get down to business.

I am accustomed to working with temporalized databases (there are never any delete or update statements, only insert statements). I typically break out each of the fields into separate tables so I only ever write the specific elements which have changed rather than the entire tuple (and if someone is trying to re-apply the current value nothing at all is written), but it doesn't really matter if you don't break out the fields since I have no intention of accessing/mutating anything directly. Instead I plan to access things via views, functions, and procedures; and mutate things via procedures. I'm sure you see the logic in this.

I've also found it easiest and best to have a single mutator for each of our conceptual entities which handles both add and edit operations. So if we had some table Foo, we would have a corresponding procedure SetFoo. The key of Foo would be OUTPUT parameters. If null is passed for the key then we know we're performing an ``add new'' operation and we return to the user the key of what they just inserted. If values are specified we know we are performing an ``edit'' or ``remove'' operation. Quite naturally my program will always except a parameter defined along the lines of ``@who nvarchar( 255 )'', and my program will always pass a value for this parameter. Being this as it may, I've been burned before so I hope your table has a column defined along the lines of:
WhenUpdated datetime not null default GetDate(),
WhoUpdated nvarchar( 255 ) not null check( '' != WhoUpdated )
I've been burned before. Any application which does not identify the user is quite obviously incorrect, and therefore should fail both loudly and immediately to make sure 1) nothing ``bad'' is happening to the database, 2) the application gets fixed quickly. Quite naturally if there are legacy programs doing stuff you'll need ``default User_Name()'', and we'll rely on the SetFoo procedure to complain about missing parameters.
The ``delete'' operation could easily be handled by parameter ``@isActive bit'', and defining the column in the underlying table as ``IsActive bit not null default 0'', which means a single SetFoo can handle add/edit/remove operations.

Since I heard people in the meeting claim there is an audit trail I assume then you are storing revision history, rather than merely tracking the last time the record was updated/inserted. I also assume you're keeping a revision history because I'm sure we've both been burned before by user-error. If this is not the case, please tell those people to stop saying ``audit trail'' because they don't have one. They have an ``audit'', but no ``trail'' by any definition of the word. This also means you can safely ignore/correct me if I ever make a request regarding ``the revision history of a given record''.

To access most of these things I'll require a view or a function. This is because I cannot join, union, restrict, or project a procedure. I need to do these things because I quite often need to add a new element for UI purposes (select null as Key, 'Select a foo' as Caption union select Key, Caption from GetFoo( currentFiscalYear ) ).

As hinted above, almost everything will need some small field suitable for display. I don't care what it is called, and since I'm working with views and functions I can derive one if necessary, but future applications might very well benefit if this were provided by the view or function.

No doubt you can see I try to treat the database in terms of ``Public Api'' and ``Private Api''. I regard the tables as private implementation details which the application/user should NOT access/mutate, and instead rely on a set of views, functions, and procedures. I hope this is appreciated. Is that not the very purpose of views, functions , and procedures in the first place?

I fully understand you have before you a very large set of systems which must continue to function after your migration. Those things which have nothing to do with Cover Crops (Cc) are not at all within my scope so I'm certainly not going to pester you on those details. This being as it may, I have attached a proposed schema for Cc (Cover Crops) and hope we can work quite well together. I have been programming in the private sector for 20 years (it would be more, but I recently took some time away to be a stay-at-home daddy), and in my experience we don't typically have Dba's; if a programmer can't do the database part then we need a new programmer, not a new Dba. Therefore I think we'll have no trouble communicating since we both understand the technical aspects and jargon (no doubt your expertise eclipses mine though). Since I'm the programmer assigned to Cc I've analyzed much of the work flow and hobbled together a schema which seems to address all of my needs, but I have no intention (nor even the capability) of doing your job for you. I'm happy to work with you, and hope you can realize I am an asset to you when it comes to the Cc portion. I am a fan of natural keys, and am not afraid of composite keys, but I'll be happy to work with whatever key is defined.

I have some requests and proposed schema almost finished, so I shall send that immediately on completion (should be today before 1500). When I do send any diagrams, you'll find they all read left-to-right and top-to-bottom. This is because in English we don't read ``mating-octopus''; we read left-to-right and top-to-bottom. If it is convenient, please do the same for me, but if not then don't worry.

Please give me a ring if you have any questions or wish to speak in depth. My voicemail still isn't configured, so email me if I don't answer.