dbo.QBM_ZDBPrincipalCreate

1  create   procedure QBM_ZDBPrincipalCreate (@SlotNumber int , @UID_QBMDBPrincipal varchar(38) , @Dummy varchar(38) , @GenProcID varchar(38) ) as
2 begin declare @LoginName nvarchar(128) = null declare @UserName nvarchar(128) = null declare @SQLcmd nvarchar(max) declare @msg nvarchar(max) BEGIN TRY
3 select top 1 @LoginName = LoginName , @UserName = UserName from QBMDBPrincipal where UID_QBMDBPrincipal = @UID_QBMDBPrincipal if isnull(@LoginName, ''
4) = ''  begin if exists (select top 1 1 from sys.database_principals where name = @UserName and type in ('S', 'U', 'G') ) begin select @SQLcmd = 'Drop User ['
5 + @UserName + ']' exec sp_executesql @SQLCmd end goto EndLabel end if dbo.QBM_FGIServerEngineProperties('AccessToServerPrincipal') = 1  begin if not exists
6 (select top 1 1 from sys.server_principals where name = @LoginName and type in ('S'  , 'U'  , 'G')  ) begin select @msg = '#LDS#ServerLogin does not exist {0}.|'
7 + @LoginName + N'|' raiserror(@msg, 18, 2) with nowait end else begin if not exists (select top 1 1 from sys.database_principals where name = @UserName
8 and type in ('S', 'U', 'G') ) begin  select @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]' exec 
9sp_executesql @SQLCmd end else begin  if not exists (select top 1 1 from sys.server_principals l join sys.database_principals u on l.sid = u.sid where 
10l.name = @LoginName and l.type in ('S', 'U', 'G') and u.name = @UserName and u.type in ('S', 'U', 'G') ) begin  if exists (select top 1 1 from sys.server_principals
11 l join sys.database_principals u on l.type = u.type where l.name = @LoginName and u.name = @UserName ) begin select @SQLcmd = 'Alter User [' + @UserName
12 + '] With Login=[' + @LoginName + ']' exec sp_executesql @SQLCmd end else begin  select @SQLcmd = 'Drop User [' + @UserName + ']' exec sp_executesql @SQLCmd
13 select @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]' exec sp_executesql @SQLCmd end end end end
14 end else begin if not exists (select top 1 1 from sys.database_principals where name = @UserName and type in ('S', 'U', 'G') ) begin  select @SQLcmd =
15 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]' exec sp_executesql @SQLCmd end else begin select @SQLcmd = 
16'Alter User [' + @UserName + '] With Login=[' + @LoginName + ']' exec sp_executesql @SQLCmd end end  declare @DBQueueElements_01 QBM_YDBQueueRaw insert
17 into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select UID_QBMDBRoleDef as UID from QBMDBPrincipalHasRoleDef
18 where UID_QBMDBPrincipal = @UID_QBMDBPrincipal ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-DBRoleDefCreate', @DBQueueElements_01 EndLabel: return END TRY
19 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
20