Powershell To Get Active Directory Users And Groups into SQL (SQLServerCentral 4/27/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Powershell To Get Active Directory Users And Groups into SQL]( - [SQL Server Closure Tables]( - [Deliver GDPR-compliant data to SQL Server teams]( - [A strategy for implementing database source control]( - [Why Don't I have a Preferred Replica?]( (From the SQLServerCentral Blogs) - [Python Jupyter Notebooks in Azure]( (From the SQLServerCentral Blogs) The Voice of the DBA The CDO One of the cornerstones of GDPR compliance is appointing someone as a data protection officer for your organization. This person is responsible for ensuring that your systems and data are safe and protected, and presumably, complying with the various articles in the GDPR. [Article 37]( notes that both a data controller and data processor shall designate a DPO in certain cases. That makes sense for companies working under GDPR jurisdiction, but for those of us outside the EU, do we need a senior level data executive? I wonder. As I've been researching the work by others and working with various clients and customers, it seems that some companies do have an executive, not necessarily in charge of protection, but in charge of data. This week, I'm wondering if any of you have a [Chief Data Officer]( in your organization. I've heard a few people say that if you do, it's a sign of maturity in your organization, where data is treated as a truly strategic and important asset. You likely have some sort of data quality metric, you many have master data management systems in place, and the organization constantly looks to extract more value from the data they store. I don't know if this is a sign of a more mature organization, but I do know that having some resources in place to ensure data quality, mapping your data estate, trying to ensure some level of consistency are signs of more data maturity. I know many of us realize data is important, and certainly our employers want our systems to run smoothly, but do they really make an effort to use data wisely? If so, then I think the company is mature. If not, maybe they're letting opportunity pass by. Lots of us data professionals do look for opportunities to better use data, to build useful reports, and find new patterns in data. Whether there's a Chief Data Officer or not, many of want to explore data and make use of information we find. I think if our organization supports that, we'll enjoy our jobs more. If not, then maybe the job is just a job for many of us. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 2.8MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Provision]( NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. [Download your free trial]( [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( Featured Contents  [] [Powershell To Get Active Directory Users And Groups into SQL]( Lowell Izaguirre from [SQLServerCentral.com]() Use Powershell to query AD for Users, Groups and GroupMembers[More »]( ---------------------------------------------------------------  [] [SQL Server Closure Tables]( Additional Articles from [SimpleTalk]() SQL Server has a couple of different ways to represent hierarchies, but they do not allow storing any additional information that is important to the relationship between objects. In this article, Phil Factor explains how to use closure tables to overcome this deficiency.[More »]( ---------------------------------------------------------------  [] [Deliver GDPR-compliant data to SQL Server teams]( The GDPR makes it essential for SQL Server teams to maintain a living data catalog and to put in place controls and processes up front to protect personal data. But this doesn’t mean you need to lock your data away or slow down your development processes. [More »]( ---------------------------------------------------------------  [] [A strategy for implementing database source control]( Much has been written on the benefits of having a database under source control, though many articles are clear on “why” but conspicuously vague on “how”. In this ahis article, David Poole describes what was required of his organization's database source control solution, how they approached the challenges, and how using Redgate SQL Source Control as a template helped. [More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Why Don't I have a Preferred Replica?]( Andy Galbraith from [SQLServerCentral Blogs]( The client reported that the log file on their main database was growing unusually large, and when they checked the...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Python Jupyter Notebooks in Azure]( Steve Jones from [SQLServerCentral Blogs]( There’s a new feature in Azure, and I stumbled on it when someone posted a link on Twitter. Apologies, I...[More »]( Question of the Day Today's Question (by Steve Jones): I have a set of data like this: CREATE TABLE CastTest ( Myval VARCHAR(20) ); GO INSERT CastTest ( Myval ) VALUES ('A') , ('My Test') , ('123') , ('123.5') , ('20180301') , ('Mar 23, 2000'), ('Jan 1, 2000'); GO I decide to filter my data by a date, but I'm worried about the data conversion. I try this code. How many rows are returned? SELECT CAST(Myval AS datetime) FROM dbo.CastTest AS ct WHERE CASE WHEN TRY_CAST(ct.Myval AS datetime) IS NOT NULL THEN CAST(ct.Myval AS datetime) END > '2000-01-01'; GO Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: CAST and CONVERT. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Expert Scripting and Automation for SQL Server DBAs]( Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): When you install SQL Server 2017 Machine Learning Services with R and Python, how many new users are added to SQL Server by default? Answer: 20 Explanation: By default, there are 20 local users created for your SQL Server Machine Learning Services instance. Ref: Install SQL Server 2017 Machine Learning Services - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Error Processing Cube from SSIS package run as a SQL Job]( - Hi, I am trying to upgrade a SQL server with Analysis Services to SQL 2017 cu5, 14.0.3023.8, (I tried CU4 before... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [principal "guest" does not exist]( - A new client of ours is trying to one of our databases, using a web interface. The user I created for... [Adding SSISDB to AlwaysOn Availability group is not working]( - Hi there I have an AlwaysOn Availability Group and am trying to add the SSISDB to it. I am following the... [Auditing]( - With GDPR looming my company has agreed that we need some auditing software to record access to data etc. I'm... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [SSIS : How to pass the value of two arrays into a variable]( - Dear All, I have a code (script task inside SSIS) that reads a csv file and gets only the necessary columns and recods... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Assigning VIEW SERVER STATE to an account (preferably WITHOUT LOGIN)]( - I'm looking at a function that has been passed to me, to assign VIEW SERVER STATE, because of the access... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [In a self-joined table, how to remove ALL records with duplicates]( - How to remove the rows 6,7, 12,13, 21 and 22? I intend to remove pair of records with duplicate Col3 and... [Group by month and year]( - Hi all, I have a date column which isn't behaving as expected... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Backup Job Failed]( - Hello guys,   I have a strange issue this morning when try to start backup job i receive this error.I try... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Can I Create A User That Can Create And Delete Tables in Specific Databases]( - I'm guessing this is possible, but just checking. We have an app that creates and deletes tables(not temp tables). I think... [Parsing Data in Column, Returning Unique Values]( - Hello, I have this sample data below: CREATE TABLE MyTable ( varchar(100), VARCHAR(100), VARCHAR(100)) INSERT INTO MyTable (,, ) VALUES (','This is... --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [Pivot to get all rows]( - I am trying to pivot and get both a summary and detail result set. The detail would get all rows... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [Maintenance cleanup task 0k button grayed out]( - Recently added a new maintenance cleanup task to an instance (SQL Server 2008). While the task worked I decided that... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Administration]( [Learning azure]( - I am a newbie to this whole Azure thing so I need some advice or direction to get started on... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [How to convert months into weeks]( - I have to split the first three months into weeks. All the weeks should end on saturday and start on... --------------------------------------------------------------- [Data Warehousing]( : [Strategies and Ideas]( [Flexible file loading]( - A big part of the data warehouse I'm currently working on is loading files from various vendors. One of them... --------------------------------------------------------------- [SQL Server 2005]( : [SS2K5 Replication]( [Log shipping]( - hi i have log shipping enabled on 1 sql instance, been working mostly fine, but all of a sudden backup logs... --------------------------------------------------------------- [SQL Server 2005]( : [T-SQL (SS2K5)]( [Updating a column with serial numbers]( - I want to update a table's column data with serial number i.e. first row 1, 2nd row 2, 3rd row... [SSIS tutoring]( - Does anyone know where I can get examples, resources or a good books for SSIS? --------------------------------------------------------------- [Career]( : [Employers and Employees]( [Database Administration Team Reporting Hierarchy]( - Morning Guys, I have an organisational question and dilemma I am trying to work through; and would like some advice. We are... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com