Код: Выделить всё
/****** Object: StoredProcedure [dbo].[CheckAtlUserName] Script Date: 03/31/2011 09:50:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[CheckAtlUserName]
@login sysname
as begin
if @login in ('bulkadmin',
'db_accessadmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_ddladmin',
'db_denydatareader',
'db_denydatawriter',
'db_owner',
'db_securityadmin',
'dbcreator',
'dbo',
'diskadmin',
'guest',
'INFORMATION_SCHEMA',
'processadmin',
'public',
'sa',
'securityadmin',
'serveradmin',
'setupadmin',
'sys',
'sysadmin') begin
raiserror ('Invalid user name ''%s'' - reserved',11,1,@login)
return
end
return 1
end
GO
/****** Object: StoredProcedure [dbo].[CheckError] Script Date: 03/31/2011 09:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[CheckError](@err int,@tcode int,@nrname varchar(20),@nrec binary(8)) as
if @err=255 begin
raiserror(50007,11,-1)
rollback transaction
end
if @err=254 begin
raiserror(50008,11,-1)
rollback transaction
end
if @err=253 begin
raiserror(50009,11,-1,@tcode,@nrname,@nrec)
rollback transaction
end
return 0
GO
/****** Object: StoredProcedure [dbo].[CreateAtlUser] Script Date: 03/31/2011 09:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[CreateAtlUser]
@login sysname,
@pwd varchar(25) = null,
@asadm bit = null,
@grp sysname = null
as begin
if @login is null return
declare @type char(1) = case when charindex(char(92),@login)=0 then 'S' else 'U' end
-- проверка зарезервированных имён
if @type='S' begin
declare @r int
exec @r=CheckAtlUserName @login
if @r=0 return
end
declare @sid varbinary(85),
@ltype char(1),
@dbname sysname,
@pwdok int,
@pid int,
@uname sysname,
@utype char(1),
@uschm sysname,
@ulogin sysname,
@ubad bit,
@rtype char(1)
exec GetAtlUserInfo @login, @pwd, @sid out, @ltype out, @dbname out, @pwdok out, @pid out, @uname out, @utype out, @uschm out, @ulogin out, @ubad out
-- проверка существующего логина
if @sid is not null and @ltype <> @type begin
raiserror ('Existing login ''%s'' has another type ''%s''',11,1,@login,@ltype)
return
end
-- проверка привязанного юзера
if @ubad=1 begin
raiserror ('User for login ''%s'' has another name ''%s'' or another type ''%s'' or another scheme ''%s''',11,1,@login,@uname,@ltype,@uschm)
return
end
-- проверка похожего юзера
if @sid is null and @uname is not null and (@utype <> @type or @uschm <> 'dbo' and @uschm <> @login or @ulogin is not null) begin
raiserror ('User with name ''%s'' has another type ''%s'' or another scheme ''%s'' or linked with another login ''%s''',11,1,@uname,@ltype,@uschm,@ulogin)
return
end
-- проверка группы
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
-- создание/модификация логина
exec ValidateAtlLogin @login, @dbname, @type, @pwdok, @pwd
if @@error > 0 return
-- создание/модификация юзера
if @pid is null begin
exec ('create user ['+@login+'] for login ['+@login+'] with default_schema=dbo')
if @@error > 0 return
end else if @ubad is null and @ulogin is null or @uschm<>'dbo' begin
declare @austmt varchar(1000) = 'alter user ['+@login+'] with '
if @ulogin is null begin
set @austmt=@austmt+'login=['+@login+']'
if @uschm<>'dbo' set @austmt=@austmt+','
end
if @uschm<>'dbo' set @austmt=@austmt+'default_schema=dbo'
exec (@austmt)
if @@error > 0 return
end
declare @newlogin bit = case when @sid is null then 1 else 0 end,
@newuser bit = case when @pid is null then 1 else 0 end,
@newgrp bit = case when @rtype is null then 1 else 0 end
exec ValidateAtlRoles @login, @asadm, @newlogin, @newuser, @grp, @newgrp
if @@error > 0 return
return 1
end
GO
/****** Object: StoredProcedure [dbo].[DeleteAtlUser] Script Date: 03/31/2011 09:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[DeleteAtlUser]
@login sysname,
@droplogin bit,
@grp sysname
as begin
if exists(select 1 from sys.database_principals where name=@login and type in ('S','U')) begin
exec ('drop user ['+@login+']')
if @@error > 0 return
end
if exists(select 1 from sys.database_principals where name=@grp and type='R') begin
begin try
exec ('drop role ['+@grp+']')
end try
begin catch
if error_number()=15144 begin
declare @rn varchar(20),@mc cursor
set @mc =
cursor fast_forward for
select u.name
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.name = @grp
and g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
open @mc
fetch next from @mc into @rn
while @@fetch_status=0
begin
exec sp_droprolemember @grp, @rn
if @@error > 0 return
fetch next from @mc into @rn
end
close @mc
deallocate @mc
exec ('drop role ['+@grp+']')
if @@error > 0 return
end else begin
declare
@s int = error_severity(),
@t int = error_state(),
@m nvarchar(2048) = error_message()
raiserror ( @m, @s, @t )
return
end
end catch
end
if @droplogin=1 and exists(select 1 from sys.server_principals where name=@login and type in ('S','U')) begin
exec ('drop login ['+@login+']')
if @@error > 0 return
end
return 1
end
GO
/****** Object: StoredProcedure [dbo].[DeleteMemoValues] Script Date: 03/31/2011 09:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteMemoValues] (@MCode INT) AS
-- Check privileges here
DELETE FROM XX$Memo WHERE M#Code=@MCode
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[SetCorpoHeaderOnDelete] Script Date: 03/31/2011 09:51:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SetCorpoHeaderOnDelete](@LastDate INT, @LastTime INT, @LastUser BINARY(8), @OriginOffice INT )AS
IF EXISTS(SELECT * FROM XX$CorpoHeaderOnDelete WHERE SPID=@@SPID)
UPDATE XX$CorpoHeaderOnDelete SET
Atl_LastDate=@LastDate,
Atl_LastTime=@LastTime,
Atl_LastUser=@LastUser,
Atl_OriginOffice=@OriginOffice
WHERE SPID=@@SPID
ELSE
INSERT INTO XX$CorpoHeaderOnDelete(
SPID,
Atl_LastDate,
Atl_LastTime,
Atl_LastUser,
Atl_OriginOffice
) VALUES(@@SPID,@LastDate,@LastTime,@LastUser,@OriginOffice)
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[ValidateAtlLogin] Script Date: 03/31/2011 09:51:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[ValidateAtlLogin]
@login sysname,
@dbname sysname,
@type char(1),
@pwdok bit,
@pwd varchar(25),
@nlogin sysname = null
as
declare @db sysname = db_name()
if @pwdok is null begin
if @type='U'
exec ('create login ['+@login+'] from windows with default_database=['+@db+']')
else
exec ('create login ['+@login+'] with password='''+@pwd+''', default_database=['+@db+'], check_policy=off')
if @@error > 0 return
end else if @nlogin is not null or @dbname is null or @pwdok=0 begin
declare @alstmt varchar(1000) = 'alter login ['+@login+'] with ',
@comma char = ''
if @nlogin is not null begin
set @alstmt=@alstmt+'name=['+@nlogin+']'
set @comma=','
end
if @dbname is null begin
set @alstmt=@alstmt+@comma+'default_database=['+@db+']'
set @comma=','
end
if @pwdok=0 set @alstmt=@alstmt+@comma+'password='''+@pwd+''''
exec (@alstmt)
if @@error > 0 return
end
GO
/****** Object: StoredProcedure [dbo].[ValidateAtlRoles] Script Date: 03/31/2011 09:51:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[ValidateAtlRoles]
@login sysname,
@asadm bit,
@newlogin bit,
@newuser bit,
@grp sysname = null,
@newgrp bit = 1
as
if @grp is not null begin
-- создание группы
if @newgrp=1 begin
exec ('create role ['+@grp+'] authorization dbo')
if @@error > 0 return
end
-- добавление в группу
if @newgrp=1 or is_rolemember(@grp,@login) <> 1 begin
exec sp_addrolemember @grp, @login
if @@error > 0 return
end
end
-- логин - админ
if @asadm=1 and (@newlogin=1 or is_srvrolemember('securityadmin',@login) <> 1) begin
exec sp_addsrvrolemember @login, securityadmin
if @@error > 0 return
end else if @asadm=0 and @newlogin=0 and is_srvrolemember('securityadmin',@login) = 1 begin
exec sp_dropsrvrolemember @login, securityadmin
if @@error > 0 return
end
-- юзер - админ
if @asadm=1 and (@newuser=1 or is_rolemember('db_owner',@login) <> 1) begin
exec sp_addrolemember db_owner, @login
if @@error > 0 return
end else if @asadm=0 and @newuser=0 and is_rolemember('db_owner',@login) = 1 begin
exec sp_droprolemember db_owner, @login
if @@error > 0 return
end
GO
/****** Object: StoredProcedure [dbo].[ModifyAtlUser] Script Date: 03/31/2011 09:54:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create 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 @login, @asadm, @newlogin, @newuser, @grp, @newgrp
if @@error > 0 return
return 1
end
GO
/****** Object: StoredProcedure [dbo].[GetAtlUserInfo] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetAtlUserInfo]
@login sysname,
@pwd varchar(25),
@sid binary(85) out,
@ltype char(1) out,
@dbname sysname out,
@pwdok bit out,
@pid int out,
@uname sysname out,
@utype char(1) out,
@uschm sysname out,
@ulogin sysname out,
@bad_linked_user bit out
as begin
select @sid=l.sid,
@ltype=l.type,
@dbname=d.name,
@pwdok=case when @pwd is null or l.type<>'S' then 1 else pwdcompare(@pwd, convert(varbinary(256),loginproperty(@login,'PasswordHash'))) end,
@pid=u.principal_id,
@uname=u.name,
@utype=u.type,
@uschm=u.default_schema_name
from sys.server_principals l
left join sys.databases d on l.default_database_name=d.name
left join sys.database_principals u on l.sid=u.sid
where l.name=@login
set @bad_linked_user=case when @pid is null then null else case when @uname<>@login or @utype<>@ltype then 1 else 0 end end
if @pid is null or @uname<>@login
select @pid=u.principal_id,
@uname=u.name,
@utype=u.type,
@uschm=u.default_schema_name,
@ulogin=l.name
from sys.database_principals u left join sys.server_principals l on u.sid=l.sid where u.name=@login
end
GO
/****** Object: StoredProcedure [dbo].[GetCurrOffice] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCurrOffice] (@Office INT OUTPUT) AS
SELECT @Office=OfficeNo FROM X$JournalConfig
IF @Office IS NULL
SELECT @Office=0
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[GetDBUserName] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetDBUserName](@spid smallint)
as
select user_name(uid) from master..sysprocesses where spid=@spid
return 0
GO
/****** Object: StoredProcedure [dbo].[GetHeader] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetHeader] (@OfficeNo INT OUTPUT,@UserNRec BINARY(8) OUTPUT) AS
DECLARE @UserLogin VARCHAR(32),@UserOffice INT,@StrPos INT,@StartPos INT,@PrevPos INT
EXEC GetCurrOffice @OfficeNo OUTPUT
DECLARE @SkipUpdateFields INT
EXEC @SkipUpdateFields=master..na_skipupdatefields @@SPID
IF @SkipUpdateFields<>1
SELECT @UserLogin=SUser_SName()
ELSE
EXEC master..na_getcurruser @@spid, @UserLogin output
SELECT @StartPos=1,@PrevPos=1
WHILE 1=1 BEGIN
SELECT @StrPos=CHARINDEX('#',SUBSTRING(@UserLogin,@StartPos,33))
IF @StrPos=0 BREAK
SELECT @PrevPos=@StartPos,@StartPos=@StrPos+@StartPos
CONTINUE
END
IF SUBSTRING(@UserLogin,@StartPos,1) BETWEEN '0' AND '9' BEGIN
SELECT @UserOffice=CONVERT(INT,SUBSTRING(@UserLogin,@StartPos,33))
SELECT @UserLogin=SUBSTRING(@UserLogin,@PrevPos,@StartPos-@PrevPos-1)
END ELSE BEGIN
SELECT @UserOffice=@OfficeNo
SELECT @UserLogin=SUBSTRING(@UserLogin,@StartPos,33)
END
SELECT @UserNRec=Atl_NRec FROM X$Users WHERE XU$LoginName=@UserLogin AND XU$UserOffice=@UserOffice
IF @UserNRec IS NULL
SELECT @UserNRec=0x8000000000000000
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[GetJournalKey] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetJournalKey]( @JNRec binary(8) output ) as
declare @db_name varchar(30),@needmax int
select @db_name=upper(db_name()),@needmax=0
declare @OriginOffice int
exec GetCurrOffice @OriginOffice output
if @JNRec is null select @JNRec=0x8000000000000000
exec master..na_getnextnrec @db_name,15,@JNRec output,@needmax output
if @needmax=1 begin
declare @max#JNRec binary(8)
select @max#JNRec=max(NREC) from X$JOURNAL
where convert(int,substring(NREC,1,2))=(@OriginOffice|0x8000)
if @max#JNRec is null select @max#JNRec=0x8000000000000000
exec master..na_getnextnrecbymax @db_name,15,@JNRec output,@needmax output,@max#JNRec
end
exec CheckError @needmax,15,'NREC',@JNRec
select @JNRec=convert(binary(2),@OriginOffice|0x8000)+substring(@JNRec,3,6)return 0
GO
/****** Object: StoredProcedure [dbo].[GetJournalMode] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetJournalMode](@Mode TINYINT OUTPUT) AS
SELECT @Mode=TypeJournal FROM X$JournalConfig
IF @Mode IS NULL
SELECT @Mode=0
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[GetJournalModeForTable] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetJournalModeForTable](@TableCode INT,@Mode TINYINT OUTPUT) AS
DECLARE @ForCorpo TINYINT,@ForPersons TINYINT,@ForRepair TINYINT
SELECT @ForCorpo=ForCorpo,@ForPersons=ForPersons,@ForRepair=ForRepair
FROM X$RegisterTables WHERE TableCode=@TableCode
IF (@ForCorpo IS NULL) OR (@ForCorpo=0 AND @ForPersons&~17=0 AND @ForRepair=0)
SELECT @Mode=0
ELSE
SELECT @Mode=1
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[GetJournalStatusForTable] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetJournalStatusForTable](@TableCode INT,@Status TINYINT OUTPUT) AS
SELECT @Status=ForCorpo FROM X$RegisterTables WHERE TableCode=@TableCode
IF @Status IS NOT NULL BEGIN
IF @Status=1
SELECT @Status=0
ELSE
SELECT @Status=1
END ELSE
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[GetMemoValue] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMemoValue] (@MCode INT, @MNRec BINARY(8)) AS
-- Check privileges here
SELECT M#Data FROM XX$Memo
WHERE M#Code=@MCode AND M#NRec=@MNRec AND M#Flag=0
RETURN 0
GO
/****** Object: StoredProcedure [dbo].[GetProcName] Script Date: 03/31/2011 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProcName](@infosize integer,@hashvalue binary(16),@info varbinary(7971)) as
select procname,memoinfo
from xx$hashvalues
where infosize=@infosize and hashvalue=@hashvalue and info=@info
return 0
GO
.