Sql-server – Making production data accessible to developers via masking

I dont see a need for 1.

You can use below script to move logins from one server to another. It uses SQLCMD and xp_cmdshell.

You can also look for a PowerShell option if you dont want to use xp_cmdshell.

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins from one server to another
-- Version  :: 1.0 for dba.stackexchange.com
--  Date    :: 05/24/2013
-- Change your paths for the output from D:\logs\ to whatever suits your need

declare     @dbid tinyint,
            @SQLText varchar(8000),
            @destserver varchar(255),

set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)

How to check which database roles can see masked columns?

Normal query which checks for users permissions seems to do the trick

    select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

I tested it on below sample script from msdn

CREATE TABLE Membership  
  (MemberID int IDENTITY PRIMARY KEY,  
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  
   LastName varchar(100) NOT NULL,  
   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);  

INSERT Membership (FirstName, LastName, Phone#, Email) VALUES   
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),  
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),  
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');  

CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON Membership TO TestUser;  


GRANT UNMASK TO TestUser;  
EXECUTE AS USER = 'TestUser';  
SELECT * FROM Membership;  
REVERT;  

now querying gives me below result