如何使用TSQL在SSR中删除用户

Question

我有一些我想删除的SSR中的用户。我尝试运行的地方：

DELETE FROM dbo.Users
WHERE dbo.Users.UserName = 'domainuser'    

我会收到以下错误：

  Msg 547, Level 16, State 0, Line 1
 The DELETE statement conflicted with the REFERENCE constraint       
 "FK_Catalog_CreatedByID". The conflict occurred in database "ReportServer",       
 table "dbo.Catalog", column 'CreatedByID'.
 The statement has been terminated.

如何删除用户（以及SSR数据库中的所有权限？

Answer 1

我遇到了我想永久从SSR数据库中删除用户的问题。因此，我制作了此SQL语法（之前备份您的DB！）：

DECLARE @username varchar(255); SET @username = '<username>';
DECLARE @ownerid uniqueidentifier; SET @ownerid = (SELECT UserID FROM Users WHERE UserName = @username)
BEGIN TRAN
  UPDATE Subscriptions SET ModifiedByID = OwnerID WHERE ModifiedByID = @ownerid
  DELETE FROM Subscriptions WHERE OwnerID = @ownerid
  DELETE FROM Schedule WHERE CreatedById = @ownerid 
  DELETE FROM PolicyUserRole WHERE UserID = @ownerid
  DELETE FROM Users WHERE UserID = @ownerid
COMMIT

用要删除的用户替换<username>，仅此而已。但是请注意，这也删除了此用户的其他数据。

Answer 2

错误很明显您在其他表中具有引用您要删除的用户表的数据。

所以您有两个选择：

1. 从用户表中删除约束，然后再次删除记录并重新申请所有约束。
2. 从引用用户表的参考表中删除数据。

Answer 3

select UserID, u.UserName
into #rs_users
from
    ReportServer.dbo.Users as u 
where userid = 1 -- expression
delete from [ReportServer].[dbo].[PolicyUserRole]
where UserID in (select UserID from #rs_users)
declare @user_name nvarchar(100), @pattern_start nvarchar(300), @pattern_end nvarchar(100), @result nvarchar(max)
declare @start_position int, @end_position int, @sec_data_id uniqueidentifier
DECLARE user_cursor CURSOR
FOR 
select UserName 
from #rs_users
OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO @user_name
WHILE @@FETCH_STATUS = 0
BEGIN
    set @pattern_start = '<Policy><GroupUserName>' + @user_name + '</GroupUserName><GroupUserId>'
    set @pattern_end   = '</Policy>'
    DECLARE secdata_cursor CURSOR
    FOR 
        select SecDataID 
        from 
            [ReportServer].[dbo].SecData as sec
        where 
            sec.XmlDescription like '%' + @pattern_start + '%' 
    OPEN secdata_cursor;
    FETCH NEXT FROM secdata_cursor INTO @sec_data_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select 
            @start_position = PATINDEX ( '%' + @pattern_start + '%' , XmlDescription ) ,
            @end_position = CHARINDEX(@pattern_end, cast(XmlDescription as nvarchar(max)), @start_position),
            @result = SUBSTRING ( XmlDescription , @start_position , @end_position+len(@pattern_end)-@start_position )  
        from [ReportServer].[dbo].SecData as sec
        where SecDataID=@sec_data_id
        -- replace user to empty
        if @start_position > 0 and @end_position > 0 and len(@result) > 0
        begin
            update [ReportServer].[dbo].SecData
            set XmlDescription = replace(cast(XmlDescription as nvarchar(max)),@result,'')
            where SecDataID=@sec_data_id
        end
        FETCH NEXT FROM secdata_cursor INTO @sec_data_id
    END;
    CLOSE secdata_cursor;
    DEALLOCATE secdata_cursor;
    FETCH NEXT FROM user_cursor INTO @user_name
END;
CLOSE user_cursor;
DEALLOCATE user_cursor;