I would use an INT Identity field to generate the numbers, and then add a computed column for the username / identity thing:
CREATE TABLE [dbo].[MyTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[member_id] AS ('MA'+CONVERT([varchar](6),[Id],0)),
[Name] [nchar](10) NOT NULL
) ON [PRIMARY]