SQL 2005 as an antipattern

After 1 month and an half working on the strange combination of EJBs and SQL 2005 I can say that not only EJBs are an antipattern but also the bloody SQL server of Microsoft.

We had so many troubles doing an automated backup and restore of a database, the last one was a permission problem that you can fix using a stored procedure (oh my God I wrote that I am using a stored procedure from Microsoft running on  Microsoft RDBMS!) that it’s shipped with that kind of RDBMS. Is very nice the code, Action=Auto_Fix.

How much easier and safer is to backup a MySql server(*)?

This problem was just the last one, and I need to yell out a bit.

STG Forums :: View topic – SQL login problems after a backup/restore
To fix this problem, run the following SQL commands:
Code:
Exec sp_change_users_login @Action = ‘Auto_Fix’, @UserNamePattern = ‘tsmith’

That’s what happens with some folks without knowledge of what are good programming practices write a RDMS. I just wanna paste here the code (part of) of the Stored Procedure. It’s SQLserver two thousand five and someone still write shit code like this:

– ERROR IF NOT AUTO_FIX –
if @Action <> ‘AUTO_FIX’
begin
raiserror(15286,-1,-1,@ActionIn)
return (1)
end

– HANDLE AUTO_FIX –
– CHECK PERMISSIONS –
if not is_srvrolemember(‘sysadmin’) = 1
begin
dbcc auditevent (130, 14, 0, NULL, @UserNamePattern, NULL, NULL, NULL, NULL, NULL)
raiserror(15247,-1,-1)
return (1)
end
else
begin
dbcc auditevent (130, 14, 1, NULL, @UserNamePattern, NULL, NULL, NULL, NULL, NULL)
end

– VALIDATE PARAMS –
if @UserNamePattern IS Null or @LoginName IS NOT Null
begin
raiserror(15600,-1,-1,’sys.sp_change_users_login’)
return (1)
end

– LOOP THRU ORPHANED USERS –
select @exec_stmt = ‘declare ms_crs_110_Users cursor global for
select name from sysusers
where name = N’ + quotename( @UserNamePattern , ””)+ ‘
and   issqluser = 1
and   sid is not NULL
and   len(sid) < = 16
and   suser_sname(sid) is null'
EXEC (@exec_stmt)
open ms_crs_110_Users
fetch next from ms_crs_110_Users into @110name

while (@@fetch_status = 0)
begin
if exists (select * from sys.server_principals where
name = @110name              -- match login name
and type in ('C', 'K'))            -- check if it is a certificate or asymmetric key login
begin
raiserror(15291,-1,-1, 'login', @110name)
deallocate ms_crs_110_Users
return (1)
end

-- IS NAME ALREADY IN USE? --
if not exists(select * from master.dbo.syslogins where loginname = @110name)
begin
-- VALIDATE PARAMS --
if @Password IS Null
begin
raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end

-- ADD LOGIN --
EXEC @ret = sys.sp_addlogin @110name, @Password, @dbname
if @ret <> 0 or suser_sid(@110name) is null
begin
raiserror(15497,16,1,@110name)
deallocate ms_crs_110_Users
return (1)
end
select @FixMode = ’1AddL’
raiserror(15293,-1,-1,@110name)
end
else
begin
– REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB –
select @FixMode = ’2UpdU’
raiserror(15292,-1,-1,@110name)
end

select @loginsid = suser_sid(@110name)
if not exists (select * from sysusers where sid = @loginsid)
begin
– LOCK USER –
BEGIN TRANSACTION
EXEC %%Owner(Name = @110name).Lock(Exclusive = 1)
– UPDATE SYSUSERS ROW –
if @@error = 0
begin
EXEC %%UserOrGroup(Name = @110name).SetSID(SID = @loginsid,
IsExternal = 0, IsGroup = 0) — may fail
if @@error <> 0
begin
ROLLBACK TRANSACTION
deallocate ms_crs_110_Users
raiserror(15063,-1,-1)
return (1)
end
end
COMMIT TRANSACTION

if @FixMode = ’1AddL’
select @cfixesaddlogin = @cfixesaddlogin + 1
else
select @cfixesupdate = @cfixesupdate + 1
end
else
raiserror(15331,-1,-1,@110name)

fetch next from ms_crs_110_Users into @110name
end — loop
close ms_crs_110_Users
deallocate ms_crs_110_Users

– REPORT AND RETURN SUCCESS –
raiserror(15295,-1,-1,@cfixesupdate)
raiserror(15294,-1,-1,@cfixesaddlogin)
return (0) — sp_change_users_login

(*) Go here if you don’t confide in me, I did that many times, with cron, from ant, never had ANY problems.