SELECT 'IF EXISTS (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN ALTER LOGIN ' +QUOTENAME(SP.name)+

			   CASE 

					WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, 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+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Alter --]

from sys.server_principals AS SP 

LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id

JOIN sys.database_principals DP ON SP.name = DP.name

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');



SELECT 'IF NOT EXISTS (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+

			   CASE 

					WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, 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+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Create --]

from sys.server_principals AS SP 

LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id

JOIN sys.database_principals DP ON SP.name = DP.name

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');





select 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SP.name + '''' AS [Login Için Yetki]

from sys.server_principals AS SP 

LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id

JOIN sys.database_principals DP ON SP.name = DP.name

WHERE SL.type IN ('S','G','U')





SELECT 'EXECUTE sp_addrolemember ''' + roles.name + ''', ''' + users.name + '''' AS [User Için Yetkiler]

 from sys.database_principals users

  inner join sys.database_role_members link

   on link.member_principal_id = users.principal_id

  inner join sys.database_principals roles

   on roles.principal_id = link.role_principal_id



end