Skip to content

Posts tagged ‘Migration’

Steps to migrate Netezza database security groups across servers

Steps to migrate Netezza database security groups across servers:

1) Run below query in source server to make a list of groups to copy. Ignore this step if you already have a list of groups to move.

nzsql -At -c ” select groupname from _v_group where groupname like ‘DEV_LOG%’ or groupname like ‘DEV_DB%’ or groupname like ‘DEV_DB1%’ or groupname like ‘DEV_DB2%’;” > groups_move.lst

2) Create a script to generate a script for create group for all the groups in groups_move.lst (created above)

more generate_create_group.sh
for tab in `cat groups_move.lst`
do
nz_ddl_group $tab
done

chmod 775 generate_create_group.sh

3) Run generate_create_group.sh script and save output to create_group.sql

generate_create_group.sh > create_group.sql

create_group.sql will have a create group script for all the required groups.

4) Run create_group.sql script on target server system database

nzsql -host targetserver -u username -pw password -f create_group.sql

or copy the script to target server and run

nzsql -f create_group.sql

5) Now we need to know the database list in target server for which these groups permissions needs to me migrated. Once you have a list move to next step.

6) On source server run below query to get all the groups permissions in a database.

nz_ddl_grant_group DBNAME > DBNAME_groupaccess.sql

Run this for each database in step 5

Verify DBNAME_groupaccess.sql and remove any unwanted group that we are not migrating.

7) Now run DBNAME_groupaccess.sql script against respective DBNAME on target server

nzsql -host targetserver -u username -pw password -f DBNAME_groupaccess.sql

or copy the script to target srever and run

nzsql -f DBNAME_groupaccess.sql

Script to list out all the SQL Server logins

Script to list out all the SQL Server logins along with its properties. The output of this script will be a create login script which can be run on the target machine where all these logins are to be migrated. You will see their are two selct queries in this script. First select query is to pull out all the SQL authenticated logins and second script will pull out all the Windows authenticated logins.

SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD=' + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID=' + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 'us_english')) + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END as 'SQL Server Logins' FROM sys.sql_logins WHERE name not like 'sa' UNION ALL SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH ' + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 'us_english')) as 'SQL Server Logins' FROM sys.server_principals WHERE type IN ('U','G')