Ir al contenido principal

SharePoint User Profile Export from the Database 

   1: SELECT a.NTName, a.Employee, g.FirstName, h.LastName, 
   2: b.Title, e.Department, a.[Office Phone], c.Email, f.Office, a.Manager
   3: ,(select top 1 f.Email from UserProfile_Full f 
   4:     where f.NTName = a.Manager
   5:     group by f.NTName, f.Email) as 'Manager Email'
   6: FROM (select
   7: a.NTName,
   8: a.RecordID,
   9: a.Manager,
  10: a.PreferredName as Employee,
  11: b.PropertyVal as [Office Phone]
  12: from UserProfile_Full a, UserProfileValue b
  13: where b.PropertyID=8 and
  14: a.RecordID=b.RecordID) a -- WorkPhone
  15: left outer join
  16: (select
  17: a.RecordID,
  18: a.PreferredName as Employee,
  19: b.PropertyVal as Title
  20: from
  21:   UserProfile_Full a, UserProfileValue b 
  22: where
  23: b.PropertyID=13 and
  24: a.RecordID=b.RecordID) b -- Title
  25: on a.RecordID=b.RecordID
  26: left outer join
  27: (select
  28: a.RecordID,
  29: b.PropertyVal as Email
  30: from
  31:   UserProfile_Full a, UserProfileValue b
  32: where
  33: b.PropertyID=9 and
  34: a.RecordID=b.RecordID) c -- WorkEmail
  35: on a.RecordID=c.RecordID
  36: left outer join
  37: (select
  38: a.RecordID,
  39: b.PropertyVal as [Cell Phone]
  40: from
  41:   UserProfile_Full a, UserProfileValue b
  42: where
  43: b.PropertyID=19 and
  44: a.RecordID=b.RecordID) d --CellPhone
  45: on a.RecordID=d.RecordID
  46: left outer join
  47: (select
  48: a.RecordID,
  49: b.PropertyVal as Department
  50: from
  51:   UserProfile_Full a, UserProfileValue b
  52: where
  53: b.PropertyID=14 and
  54: a.RecordID=b.RecordID) e --Department
  55: on a.RecordID=e.RecordID
  56: left outer join
  57: (select
  58: a.RecordID,
  59: b.PropertyVal as Office
  60: from
  61:   UserProfile_Full a, UserProfileValue b
  62: where
  63: b.PropertyID=11 and
  64: a.RecordID=b.RecordID) f --Office
  65: on a.RecordID=f.RecordID
  66: left outer join
  67: (select
  68: a.RecordID,
  69: b.PropertyVal as FirstName
  70: from
  71:   UserProfile_Full a, UserProfileValue b
  72: where
  73: b.PropertyID=4 and
  74: a.RecordID=b.RecordID) g --FirstName
  75: on a.RecordID=g.RecordID
  76: left outer join
  77: (select
  78: a.RecordID,
  79: b.PropertyVal as LastName
  80: from
  81:   UserProfile_Full a, UserProfileValue b
  82: where
  83: b.PropertyID=5 and
  84: a.RecordID=b.RecordID) h --LastName
  85: on a.RecordID=h.RecordID
  86: order by a.NTName

Etiquetas:

Comentarios

Publicar un comentario

Entradas populares de este blog

Get SharePoint Online Site and SubSites permission using PowerShell

The below PowerShell script retrieves the following for the given SharePoint Online Site All the Sub-site's URL Security group attached with each Sub-site with their permission level Prerequisites: This PowerShell script uses the latest version of SharePoint Online PnP Module. Download the installer from https://github.com/SharePoint/PnP-PowerShell/releases  Install-Module SharePointPnPPowerShellOnline  Install-Module - Name ' SharePointPnP.PowerShell.Commands.Files.Recurse ' function  connect - site( $webs , $creds ){    Connect - PNPonline  - Url  $webs   - Credentials  $cred     }    function  get - sitepermission( $web , $cred ){    $rec =@()    connect - site  - webs  $web   - creds  $cred     if ( $web   - eq  $parentsitename )  {  #Write-Host "Parent site permission" $web   $Pgroups =Get - PNPGroup  foreach ( $Pgroup   in   $Pgroups )  {  $DLGP  =  ""   |   Select   "SiteUrl" , "GroupName" , "Permiss
Publicar un comentario
Leer más

Find and Delete Orphaned Users in SharePoint

Fuente: http://www.sharepointdiary.com/2012/09/find-and-delete-orphaned-users-in-sharepoint.html Orphaned User? Who are they? Orphaned users are those who have been disabled/removed from Active Directory, but still have permissions to sites, lists and items. Internally, SharePoint keeps them in " UserInfo " table of the content database for meta-data such as created/modified by fields. Its unavoidable in any organization where employees constantly on-boarding and off-boarding. Its really difficult to manage, when it comes to thousands of sub-sites, sites, libraries and lists with their own sets of permissions. Why we care about Orphaned users? It is a best practice to delete orphaned users to keep the farm clean & organized. Also this will solve the problem of deleted active directory users still appearing on the people picker which was discussed here  People Picker not showing users from Active Directory? . If you know the user base or criteria then you can use: Clea
Publicar un comentario
Leer más

Conexión desde casa a una VPN sin perder salida a internet

Solución, asumiendo que estas en Windows: Panel de Control, Conexiones de Red. Clic derecho en la VPN, dale a propiedades. Anda a la pestaña de "Funciones de Red" y selecciona Protocolo Internet TCP/IP y clic en el botón "Propiedades". Ahora hazle clic al botón "Opciones Avanzadas..."En la pestaña "General", desmarca la opción que dice "Usar la puerta de enlace predeterminada en la red remota". Dale a aceptar a todas las ventanitas de opción, y ahora conéctate a la VPN nuevamente. Con eso deberías entrar a la VPN sin perder la conexión local de tu red e internet.
Publicar un comentario
Leer más