[SA 9.0.157 Pro BETA] - Schema comparison help

[SA 9.0.157 Pro BETA] - Schema comparison help

I need help with modifying (creating, if needed) queries and templates. I noticed that there were some changes, eg. it no longer generates code with incorrect syntax for users (WITH DEFAULT SCHEMA) but it also creates code with WITHOUT LOGIN disregarding the fact that the original users had logins.

#1. I need to modify (or create new) queries and templates to create associated logins with passwords if they don't exist preceding the CREATE USER part.

#2. I also noticed that while both users and roles are created, they are not connected (no ALTER ROLE <role> ADD MEMBER <user> statements in the generated code). I'd like to include those as well, but...

I've read through the help chapter 25 and while I regard the stuff you've put together there a plain miracle, the information found there's is simply too vast to comprehend (and use) in one run. You know me well enough to believe I'm more than intrigued when it comes to experimenting or stretching SA's limits but since I've got a dire time pressure at hand with absolutely no room for maneuvering, I'd like to request help to put those two together. What settings should be used for them and how?

Last edited by gemisigo on Mon Oct 10, 2016 9:06 am; edited 1 time in total

I've asked for help from the team. Hope they will come to the rescue.

Here is a copy of the response I got

1. Change guery "Database Users" as below

Code:

SELECT
s.name AS [User name],
'User' AS [Target type],
s.default_database_name AS [Default database],
s.default_language_name AS [Default language],
CASE WHEN l.principal_id IS NULL THEN 1 ELSE 0 END AS [Is OS Name],
CASE l.is_policy_checked WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END AS [Is policy checked],
CASE l.is_expiration_checked WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END AS [Is expiration checked]
FROM master.sys.server_principals s
LEFT JOIN master.sys.sql_logins l
ON l.principal_id = s.principal_id
WHERE s.[type] IN ('G','U','S')
ORDER BY s.name

2. Change template "User Server (CREATE)", Condition: Is OS Name=0

	Code:
	CREATE LOGIN [$NAME$] WITH PASSWORD = '/{ password }/' DEFAULT_DATABASE = [$DEFAULT DATABASE$], DEFAULT_LANGUAGE = [$DEFAULT LANGUAGE$], CHECK_POLICY = $IS POLICY CHECKED$, CHECK_EXPIRATION = $IS EXPIRATION CHECKED$

3. Add template "User Windows (CREATE)", Condition: Is OS Name=1

	Code:
	CREATE LOGIN [$NAME$] FROM WINDOWS$ DEFAULT_DATABASE = [{DEFAULT DATABASE}]$

Thank you very much, I've created/modified the said settings and will test them tomorrow.

They seem to work very well, thank you very much.