SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.157 Pro BETA] - Schema comparison help

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.157 Pro BETA] - Schema comparison help
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.0.157 Pro BETA] - Schema comparison help Reply with quote
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
Wed Oct 05, 2016 12:31 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
I've asked for help from the team. Hope they will come to the rescue.
Wed Oct 05, 2016 10:55 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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}]$

Fri Oct 07, 2016 12:20 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Thank you very much, I've created/modified the said settings and will test them tomorrow.
Sat Oct 08, 2016 5:55 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
They seem to work very well, thank you very much.
Wed Oct 12, 2016 3:14 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.