Solution:
CREATE TABLE [dbo].[driver](
[dbname] [varchar](100) NULL,
[DataPath] [nvarchar](500) NULL,
[LogPath] [nvarchar](500) NULL
) ON [PRIMARY]
GO
/*
Generate Logins for a specific list of databases
*/
Set NoCount On
IF OBJECT_ID('tempdb..#TMP') IS NOT NULL DROP TABLE #TMP
IF OBJECT_ID('tempdb..#TMPDBS') IS NOT NULL DROP TABLE #TMPDBS
IF OBJECT_ID('tempdb..#TMPCMDS') IS NOT NULL DROP TABLE #TMPCMDS
Create Table #TMPDBS (ID int Identity(1,1), DBName varchar(150))
Insert into #TMPDBS (DBName) Select DBName from msdb.dbo.driver order by dbname asc
Declare @DB varchar(150)
Create Table #TMP (ID int Identity(1,1), DBName varchar(150), LoginName varchar(100), [SID] varbinary(max))
Create Table #TMPCMDS (LoginName varchar(100), CMDS nvarchar(max))
Declare @Total int
Set @Total = (Select count(*) from #TMPDBS)
While Exists (Select 1 from #TMPDBS)
Begin
Set @DB = (Select top 1 dbname from #TMPDBS)
Declare @ID int
Set @ID = (Select ID from #TMPDBS where dbname = @DB)
Declare @MSG varchar(100)
Set @MSG = convert(varchar(50),'Processing...') + convert(varchar(10),@ID) + '/' + convert(varchar(10),@Total) + '...[' + @DB + ']'
RAISERROR (@MSG, 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:01';
Declare @DBname varchar(150)
Declare @SQL nvarchar(max)
Set @SQL = 'Insert into #TMP (DBName, LoginName, [SID])
Select t1.dbname, t2.name, t2.[sid]
from master.dbo.syslogins t1
inner join ' + @DB + '.dbo.sysusers t2 on t1.[sid] = t2.[sid]
where t2.islogin = 1 and t2.[sid] is not null and t2.name not in (''dbo'')' + ''
Exec sp_executesql @SQL
Declare @LoginName varchar(100)
Set @LoginName = (Select top 1 [LoginName] from #TMP)
While Exists (Select 1 from #TMP)
Begin
Declare @command varchar(1000)
Declare @SQLCmds nvarchar(max)
Set @SQLCmds = 'Insert into #TMPCMDS (LoginName, CMDS) ' + 'Select ' + '''' + @LoginName + '''' + ',' + '''' + 'EXEC dbo.sp_help_revlogin [' + @LoginName + ']' + ''''
Exec sp_executesql @SQLCmds
Delete from #TMP where LoginName = @LoginName
Set @LoginName = (Select top 1 [LoginName] from #TMP)
End
Delete from #TMPDBS where DBname = @DB
End
--Remove Duplicates as a User can Exists in more than one database
;WITH cte AS (
SELECT[cmds],
row_number() OVER(PARTITION BY cmds ORDER BY cmds) AS [rn]
FROM #TMPCMDS
)
DELETE cte WHERE [rn] > 1;
Select CMDS as 'Script' from #TMPCMDS order by LoginName ASC
IF OBJECT_ID('tempdb..#TMP') IS NOT NULL DROP TABLE #TMP
IF OBJECT_ID('tempdb..#TMPDBS') IS NOT NULL DROP TABLE #TMPDBS
IF OBJECT_ID('tempdb..#TMPCMDS') IS NOT NULL DROP TABLE #TMPCMDS
|
|
|
|
|
|