CREATE TABLE #ToDo
(
RID TINYINT
,MyLogin varchar(128)
,MyScript varchar(max)
)
CREATE TABLE #MyTemp
(
RID INT IDENTITY(1,1) Primary Key
,LoginName nvarchar(max)
,DBname nvarchar(max)
,Username nvarchar(max)
,AliasName nvarchar(max)
)
INSERT INTO #MyTemp
(LoginName,DBname,Username,AliasName)
EXEC master..sp_msloginmappings
CREATE TABLE #Logins
(
RID INT IDENTITY(1,1) Primary Key
,LoginName nvarchar(max)
,DBname nvarchar(max)
,Username nvarchar(max)
,AliasName nvarchar(max)
)
INSERT INTO #Logins
(LoginName,DBname,Username,AliasName)
SELECT LoginName,DBname,Username,AliasName
FROM #MyTemp
WHERE dbname IS NOT NULL
AND SUBSTRING(LoginName,1,1) != '#'
AND SUBSTRING(LoginName,1,3) != 'NT '
CREATE TABLE #Alters
(
RID INT IDENTITY(1,1) Primary Key
,LoginName nvarchar(max)
,ALTERED nvarchar(max)
)
DECLARE @CurRow INT = 1
DECLARE @MaxRow INT
DECLARE @LoginName varchar(128)
DECLARE @DBName varchar(256)
DECLARE @MySQL varchar(max)
SELECT @MaxRow = MAX(RID)
FROM #Logins
WHILE @CurRow <= @MaxRow
BEGIN
SELECT @LoginName = LoginName
,@DBName = DBName
FROM #Logins
WHERE RID = @CurRow
;WITH MyCTE
AS
(
SELECT DP1.name AS DatabaseRoleName
, isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
AND DP2.name Like @LoginName
)
INSERT INTO #Alters
(LoginName,ALTERED)
SELECT @LoginName, 'USE '+@DBName+';'+char(10)+char(13)+'GO;'+char(10)+char(13)+ 'EXEC sp_addrolemember '''+DatabaseRoleName+''', '''+@LoginName+''';'
FROM MyCTE
SET @CurRow = @CurRow + 1
END
INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 0, MyLogin = REPLACE(REPLACE(QUOTENAME(SP.name), '[',''),']','')
,'CREATE LOGIN ' +
QUOTENAME(SP.name)
+CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = '' '' '
+ ' , CHECK_EXPIRATION = '
+CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = '
+CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END ELSE '
FROM WINDOWS WITH' END
+' DEFAULT_DATABASE=[' +SP.default_database_name+ '],
DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] '
COLLATE SQL_Latin1_General_CP1_CI_AS AS MyScript
FROM sys.server_principals AS SP
LEFT JOIN sys.sql_logins AS SL
ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');
INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 2
, MyLogin = SL.name
,'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''' AS MyScript
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR
ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL
ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name <> ('sa');
INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 3
,MyLogin = SP.name
,CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END
+ ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']'
+ CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN '' ELSE ' WITH GRANT OPTION' END collate database_default AS MyScript
FROM sys.server_permissions AS SrvPerm
JOIN sys.server_principals AS SP
ON SrvPerm.grantee_principal_id = SP.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' )
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');
INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 4, MyLogin = LoginName, MyScript = ALTERED
FROM #Alters
ORDER BY LoginName, ALTERED
--SELECT * FROM #ToDo
SELECT RID, MyLogin, MyScript
FROM #ToDo where MyLogin not like 'CLEANHARBORS\%'
ORDER BY MyLogin, RID, MyScript
-- cleanup
DROP TABLE #MyTemp
DROP TABLE #Logins
DROP TABLE #Alters
DROP TABLE #ToDo
No comments:
Post a Comment