27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #Tmp1
(
[用户名] NVARCHAR(50),
[过期日期] DATETIME
)
INSERT INTO #Tmp1
( 用户名, 过期日期 )
SELECT 'AA','2015-01-01'
UNION ALL
SELECT 'BB','2015-01-02'
UNION ALL
SELECT 'CC','2015-02-02'
UNION ALL
SELECT 'DD','2015-03-02'
DECLARE @Sql NVARCHAR(max)
SELECT @Sql=A.[Sql]
FROM (SELECT 'EXEC (''ALTER LOGIN ' + 用户名 + ' DISABLE'');'
from #Tmp1
where 过期日期<getdate() FOR XML PATH('')) AS A(Sql)
EXEC sys.sp_executesql @Sql
create procedure P_DisableUser
begin
select row_number() over(order by id desc) ID,用户名 Name
into #tmp
from A where expire_dt <= getdate()
declare @count int
select @count = count(1) from #tmp
if @count > 0
begin
declare @i int
,@Name VARCHAR(50),@sql varchar(200)
set @i = 1
while @i <= @count
begin
select @Name = Name from #tmp where ID = @i
set @sql ='ALTER LOGIN ' + @Name + ' disable'
exec(@sql)
set @i = @i + 1
end
end
drop table #tmp
end
declare @username as varchar(50),@sql varchar(100);
declare Cur cursor fast_forward --定义只读游标
for
select 用户名 from A where 过期日期>getdate()
open Cur --打开
fetch next from Cur into @username --获取数据到变量@username
while @@FETCH_STATUS==0 --有数据可循环
begin
select @sql ='ALTER LOGIN '+ @username+' disable'
exec(@sql)
fetch next from Cur into @username --获取下一条
end
close Cur --关闭游标
deallocate Cur --删除游标
declare @username as varchar(50),@sql varchar(100);
declare Cur cursor fast_forward --定义只读游标
for
select 用户名 from A where 过期日期>getdate()
open Cur --打开
fetch next from Cur into @username --获取数据到变量@username
while @@FETCH_STATUS==0 --有数据可循环
begin
select @sql ='ALTER LOGIN '+ @username+' disable'
exec(@sql)
end
close Cur --关闭游标
deallocate Cur --删除游标
select 用户名 from A where 过期日期>getdate()
但是他不是一个啊,而是大于等于0个,
禁用用户名
ALTER LOGIN sa DISABLE
如果是其他编程语言,我可以用变量和循环,但这个SQL不熟悉,不知道该怎么把他们写在一起,希望好心人指导下,谢谢。