DECLARE @login VARCHAR(100) DECLARE @CommandToExecute VARCHAR(8000) DECLARE login_cur CURSOR FAST_FORWARD FOR SELECT [name] FROM MASTER..syslogins WHERE name NOT LIKE '%#%' AND name NOT LIKE '%\%' AND name NOT IN('sa', 'admin') ORDER BY name OPEN login_cur; FETCH NEXT FROM login_cur INTO @login WHILE @@FETCH_STATUS=0 BEGIN SET @CommandToExecute='USE CHECKDB' +char(13)+char(10) +'IF EXISTS(SELECT NAME FROM SYSUSERS )' +char(13)+char(10) + 'BEGIN' +char(13)+char(10) +' DROP USER '+'['+@login+']' +char(13)+char(10) +'END' +char(13)+char(10) +'CREATE USER '+'['+@login+']'+' FOR LOGIN '+'['+@login+']' +char(13)+char(10) +char(13)+char(10) +'USE CHECKDB' +char(13)+char(10) +'GRANT INSERT TO '+'['+@login+']' +char(13)+char(10) print (@CommandToExecute) --exec (@CommandToExecute) FETCH NEXT FROM login_cur INTO @login END CLOSE login_cur; DEALLOCATE login_cur;
