info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Generate Logins for a specific list of databases for Migration purposes
by BF (Principal Consultant; Architecture; Engineering)
2018-05-06










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