set quoted_identifier off
go
if exists(select * from dbo.sysobjects where name = 'REPAIR$Users' and xtype = 'P')
drop procedure dbo.REPAIR$Users
go
create procedure dbo.REPAIR$Users (@dbname sysname, @user varchar(4000) = null)
as
begin
set nocount on
Declare
@is_nt tinyint,
@names sysname,
@sql varchar(8000),
@sid varbinary(85)
if db_id(@dbname) is null
begin
raiserror ('Указанная БД не существует', 16, 1)
return (-1)
end
if (is_srvrolemember('sysadmin', suser_name()) <> 1) or (is_srvrolemember('securityadmin', suser_name()) <> 1)
begin
raiserror ('Недостаточно прав', 16, 1)
return(-1)
end
if object_id('tempdb..#unames') is not null
drop table #unames
create table #unames (
name varchar(128),
is_nt tinyint
)
exec sp_configure 'allow updates', 1
reconfigure with override
select @sql = "select name, isntname from " + @dbname + ".dbo.sysusers (nolock) where sid is not null " +
"and name not in ('dbo', 'guest') and name " +
case when @user is not null and @user != ''
then " in (select VARCHAR_RESULT from MANAGE.dbo.UR_StringToTable(' + @user + ', 'varchar', ';'))"
else " = name "
end
-- print @sql
insert into #unames exec(@sql)
Declare
mcur cursor local fast_forward for select name, is_nt from #unames
Open mcur
fetch next from mcur into @names, @is_nt
while @@fetch_status <> -1
begin
if @is_nt = 1
begin
select @sql = "select sid from master.dbo.syslogins where name like '%" + char(92) + @names + "'"
--print @sql
exec(@sql)
if @@rowcount > 1
print "Ошибка"
else
select @sql = "update " + @dbname + ".dbo.sysusers set sid = (select sid from master.dbo.syslogins where name like '%" + char(92) + @names + "')" +
" where name = '" + @names + "'"
end
else
select @sql = "update " + @dbname + ".dbo.sysusers set sid = (select sid from master.dbo.syslogins where name = '" + @names + "')" +
" where name = '" + @names + "'"
--print @sql
exec(@sql)
fetch next from mcur into @names, @is_nt
end
close mcur
deallocate mcur
exec sp_configure 'allow updates', 0
reconfigure with override
end
go
set quoted_identifier on
go
CREATE FUNCTION dbo.UR_StringToTable
(
@string varchar(8000),
@data_type varchar(10),
@delimiter varchar(10)
)
RETURNS @TABLE TABLE (ID int, INT_RESULT int, DATE_RESULT datetime, VARCHAR_RESULT varchar(8000))
AS
BEGIN
declare
@StartLoc int,
@EndLoc int,
@Len int,
@Val varchar(8000),
@id int
if len(@delimiter) = 0
return
select @StartLoc = 1, @EndLoc = 1, @Len = len(@string), @id = 1
while @Len > 0
begin
select @EndLoc = charindex(@delimiter, @string, @StartLoc)
if @EndLoc = 0
select @EndLoc = @Len + 1, @Len = 0
select @Val = ltrim(substring(@string, @StartLoc, @EndLoc - @StartLoc))
if @Val <> ''
begin
if upper(@data_type) = 'INT'
insert @TABLE (ID, INT_RESULT)
select @id, cast(@Val as int)
else if upper(@data_type) = 'VARCHAR'
insert @TABLE (ID, VARCHAR_RESULT)
select @id, @Val
else if upper(@data_type) = 'DATETIME'
insert @TABLE (ID, DATE_RESULT)
select @id, cast(@Val as datetime)
end
select @StartLoc = @EndLoc + len(@delimiter), @id = @id + 1
end
return
END
|