Галактика в вопросах и ответах

Код: Выделить всё

USE [galacf]
GO
/****** Object:  StoredProcedure [dbo].[ModifyAtlUser]    Script Date: 04.04.2014 9:07:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ModifyAtlUser]
 @login sysname,
 @nlogin sysname = null,
 @pwd varchar(25) = null,
 @asadm bit = null,
 @drop_unused_login bit = null,
 @grp sysname = null

as begin

if @login is null return

if @nlogin is null or @nlogin=@login begin
 declare @r int
 exec @r=CreateAtlUser @login, @pwd, @asadm, @grp
 return @r
end

declare @ntype char(1) = case when charindex(char(92),@nlogin)=0 then 'S' else 'U' end

if @ntype='S' begin
 exec CheckAtlUserName @nlogin
 if @@error > 0 return
end

if @drop_unused_login is null set @drop_unused_login=0

declare @nsid varbinary(85),
        @nltype char(1),
        @ndbname sysname,
        @npwdok int,
        @npid int,
        @nuname sysname,
        @nutype char(1),
        @nuschm sysname,
        @nulogin sysname,
        @nubad bit

exec GetAtlUserInfo @nlogin, @pwd, @nsid out, @nltype out, @ndbname out, @npwdok out, @npid out, @nuname out, @nutype out, @nuschm out, @nulogin out, @nubad out

declare @type char(1) = case when charindex(char(92),@login)=0 then 'S' else 'U' end,
        @sid varbinary(85),
        @ltype char(1),
        @dbname sysname,
        @pwdok int,
        @pid int,
        @uname sysname,
        @utype char(1),
        @uschm sysname,
        @ulogin sysname,
        @ubad bit

exec GetAtlUserInfo @login, @pwd, @sid out, @ltype out, @dbname out, @pwdok out, @pid out, @uname out, @utype out, @uschm out, @ulogin out, @ubad out

declare @use_nlogin bit = case when @drop_unused_login=0 or @type='U' or @ntype='U' or @sid is null /*or @ltype<>@type or @ubad=1*/ then 1 else 0 end

declare @use_nuser bit = case when @pid is null or @ubad=1 or @type<>@ntype then 1 else 0 end

if @use_nlogin=1 and @nubad=1 begin
 raiserror ('В БД к логину %s привязан некорректный юзер',11,1,@nlogin)
 return
end

declare @rtype char(1)

if @grp is not null begin
 select @rtype=r.type from sys.database_principals r where r.name=@grp
 if coalesce(@rtype,'R')<>'R' begin
  raiserror ('''%s'' has bad type. Cure this',11,1,@grp)
  return
 end
end

if @use_nlogin=1 begin

 if @drop_unused_login=1 and @sid is not null and @ltype=@type and @ubad<>1
  begin try
   exec ('drop login ['+@login+']')
  end try
  begin catch
  end catch

 exec ValidateAtlLogin @nlogin, @dbname, @ntype, @npwdok, @pwd
 if @@error > 0 return

end else begin

 if @nsid is not null begin
  exec ('drop login ['+@login+']')
  if @@error>0 return
 end

 exec ValidateAtlLogin @login, @dbname, @type, @pwdok, @pwd, @nlogin
 if @@error > 0 return
end

if @use_nuser=1 begin

 if @pid is not null
  begin try
   exec ('drop user ['+@uname+']')
  end try
  begin catch
  end catch

 if @npid is null begin
  exec ('create user ['+@nlogin+'] for login ['+@nlogin+'] with default_schema=dbo')
  if @@error > 0 return
 end else if @nuschm<>'dbo' begin
  exec ('alter user ['+@nlogin+'] with default_schema=dbo')
  if @@error > 0 return
 end
end else begin

 if @nuname=@nlogin begin
  exec ('drop user ['+@nuname+']')
  if @@error > 0 return
 end

 exec ('alter user ['+@login+'] with name=['+@nlogin+'],login=['+@nlogin+'],default_schema=dbo')
 if @@error > 0 return
end

declare @newlogin bit = case when @use_nlogin=1 and @nsid is null then 1 else 0 end,
        @newuser bit = case when @use_nuser=1 and @npid is null then 1 else 0 end,
        @newgrp bit = case when @rtype is null then 1 else 0 end
exec ValidateAtlRoles @nlogin, @asadm, @newlogin, @newuser, @grp, @newgrp
if @@error > 0 return

return 1
end