Archive

Monthly Archives: March 2013

Many comments praise the use of adding a linked server to AD and creating a view to capture user information.  There is a 1000 record limit, so that presents an obstacle to work around.  I struggled with permissions and never got a linked server to AD to work.  I’m not a Domain Admin and have limited exposure to AD in general.  Links to those methods are below.

Using Power shellflat filesSSIS package and Agent Job I created system to capture AD information on an hourly basis.  I experimented with the Power Shell Active Directory module a lot.  Having never had access to AD, it was a whole new world learning the structure and query syntax.  I made scripts for user group membership, computers  and here is the script to capture user information.

Import-module ActiveDirectory

#————————————————————-

# User details list

#————————————————————-
$cr = “`r” #Carriage Return
$tq = “`”” #Text Qualifier
#Column Headings
“{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}{14}{15}{16}{17}{18}{19}{20}{21}{22}{23}{24}{25}{26}{27}{28}{29}{30}{31}”-f`

(“AccountExpirationDate”+”|”),`
($tq+”AccountExpires”+$tq+”|”),`
($tq+”assistant”+$tq+”|”),`
($tq+”Deleted”+$tq+”|”),`
($tq+”Department”+$tq+”|”),`
($tq+”DistinguishedName”+$tq+”|”),`
($tq+”EmailAddress”+$tq+”|”),`
($tq+”EmployeeNumber”+$tq+”|”),`
($tq+”Enabled”+$tq+”|”),`
($tq+”extensionAttribute4″+$tq+”|”),`
($tq+”extensionAttribute5″+$tq+”|”),`
($tq+”extensionAttribute6″+$tq+”|”),`
($tq+”extensionAttribute7″+$tq+”|”),`
($tq+”extensionAttribute9″+$tq+”|”),`
($tq+”extensionAttribute11″+$tq+”|”),`
($tq+”extensionAttribute12″+$tq+”|”),`
($tq+”extensionAttribute13″+$tq+”|”),`
($tq+”extensionAttribute15″+$tq+”|”),`
($tq+”GivenName”+$tq+”|”),`
($tq+”HomePhone”+$tq+”|”),`
($tq+”Initials”+$tq+”|”),`
($tq+”LastLogonDate”+$tq+”|”),`
($tq+”Manager”+$tq+”|”),`
($tq+”MobilePhone”+$tq+”|”),`
($tq+”Modified”+$tq+”|”),`
($tq+”Name”+$tq+”|”),`
($tq+”Office”+$tq+”|”),`
($tq+”OfficePhone”+$tq+”|”),`
($tq+”SamAccountName”+$tq+”|”),`
($tq+”Surname”+$tq+”|”),`
($tq+”Title”+$tq),`
$cr|
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\LG0123SQL\Process\Spool\AdUser.txt
#Data Rows
Get-ADUser -Filter * -Properties * |
ForEach-object {
$AccountExpirationDate = $_.AccountExpirationDate
$AccountExpires = $_.AccountExpires
$assistant = $_.assistant
$Deleted = $_.Deleted
$Department = $_.Department
$DistinguishedName = $_.DistinguishedName
$EmailAddress = $_.EmailAddress
$EmployeeNumber = $_.EmployeeNumber
$Enabled = $_.Enabled
$extensionAttribute4 = $_.extensionAttribute4
$extensionAttribute5 = $_.extensionAttribute5
$extensionAttribute6 = $_.extensionAttribute6
$extensionAttribute7 = $_.extensionAttribute7
$extensionAttribute9 = $_.extensionAttribute9
$extensionAttribute11 = $_.extensionAttribute11
$extensionAttribute12 = $_.extensionAttribute12
$extensionAttribute13 = $_.extensionAttribute13
$extensionAttribute15 = $_.extensionAttribute15
$GivenName = $_.GivenName
$HomePhone = $_.HomePhone
$Initials = $_.Initials
$LastLogonDate = $_.LastLogonDate
$Manager = $_.Manager
$MobilePhone = $_.MobilePhone
$Modified = $_.Modified
$Name = $_.Name
$Office = $_.Office
$OfficePhone = $_.OfficePhone
$SamAccountName = $_.SamAccountName
$Surname = $_.Surname
$Title = $_.Title
“{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}{14}{15}{16}{17}{18}{19}{20}{21}{22}{23}{24}{25}{26}{27}{28}{29}{30}{31}”-f`
([string]$_.AccountExpirationDate+”|”),`
($tq+[string]$_.AccountExpires+$tq+”|”),`
($tq+[string]$_.assistant+$tq+”|”),`
($tq+[string]$_.Deleted+$tq+”|”),`
($tq+[string]$_.Department+$tq+”|”),`
($tq+[string]$_.DistinguishedName+$tq+”|”),`
($tq+[string]$_.EmailAddress+$tq+”|”),`
($tq+[string]$_.EmployeeNumber+$tq+”|”),`
($tq+[string]$_.Enabled+$tq+”|”),`
($tq+[string]$_.extensionAttribute4+$tq+”|”),`
($tq+[string]$_.extensionAttribute5+$tq+”|”),`
($tq+[string]$_.extensionAttribute6+$tq+”|”),`
($tq+[string]$_.extensionAttribute7+$tq+”|”),`
($tq+[string]$_.extensionAttribute9+$tq+”|”),`
($tq+[string]$_.extensionAttribute11+$tq+”|”),`
($tq+[string]$_.extensionAttribute12+$tq+”|”),`
($tq+[string]$_.extensionAttribute13+$tq+”|”),`
($tq+[string]$_.extensionAttribute15+$tq+”|”),`
($tq+[string]$_.GivenName+$tq+”|”),`
($tq+[string]$_.HomePhone+$tq+”|”),`
($tq+[string]$_.Initials+$tq+”|”),`
($tq+[string]$_.LastLogonDate+$tq+”|”),`
($tq+[string]$_.Manager+$tq+”|”),`
($tq+[string]$_.MobilePhone+$tq+”|”),`
($tq+[string]$_.Modified+$tq+”|”),`
($tq+[string]$_.Name+$tq+”|”),`
($tq+[string]$_.Office+$tq+”|”),`
($tq+[string]$_.OfficePhone+$tq+”|”),`
($tq+[string]$_.SamAccountName+$tq+”|”),`
($tq+[string]$_.Surname+$tq+”|”),`
($tq+[string]$_.Title+$tq),`
$cr
} |
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\LG0123SQL\Process\Spool\AdUser.txt -append # output

The output does need to be a UNC path. Once the flat files are created then SSIS and T-sql can take it from there to update the database of users and computers.  The project is an inventory of users, computers and the software installed on their computers.  The firm users have some expensive software and the licensing must be closely tracked.

Here are the links to other discussion on pulling in AD to SQL Server.

http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users

http://dbatasks.blogspot.co.uk/2013/03/getting-data-out-of-active-directory.html

There are many scripts in blogs and forums to remove a user from all databases in a SQL Server instance.  I want to keep as simple as possible and just drop the database user account.  This script will error on the databases where the users owns a schema.  I want to know those databases and want to retain the login.  Plus having heard so many warnings about cursors I don’t use them.  Some would say a loop in a WHILE statement is the same thing as a cursor.

Simply put, get a list of databases to check for the user, loop through each, and drop the user if it exists.

DECLARE @Databasename VARCHAR(128)
, @Command VARCHAR(255)
, @UserName VARCHAR(128)

SELECT @UserName = ” — Enter a User

DECLARE @DBList TABLE
(DatabaseName VARCHAR(128))

INSERT INTO @DBList
( DatabaseName )
SELECT name
FROM master.sys.databases
WHERE name <> ‘master’

WHILE (SELECT COUNT(*) FROM @DBList) > 0
BEGIN
SELECT TOP 1 @Databasename = Databasename
FROM @DBList

SELECT @Command = ‘USE [‘ + @Databasename + ‘] IF EXISTS (SELECT * FROM ‘ + @Databasename + ‘.sys.database_principals WHERE name = N’
+ ”” + @UserName + ”” + ‘) DROP USER [‘+ @UserName +’]’

PRINT @Command
EXECUTE (@Command )

DELETE FROM @DBList WHERE DAtabaseName = @Databasename
END

 

Centralize SQL Server maintenance with native tool in my goal. Scripts are saved in a database table, exported to a .bat in the file system and executed from a SQL Server Agent job.

The basic statement in the command batch file looks like this:

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH NO_INFOMSGS ” 

I build the statement using the server database ID rather than database name.  There are no naming convention rules like “database names have no spaces” or “do not begin with a number” for systems not created by me.  Just easier to reference databases from numbers than names in the Dynamic SQL code within the stored procedures than handling complex database names.

I want to have a .log file error message result output.  I can then add a step to my job that looks for error message files and forward the file in email to me as notification of errors.

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH ALL_ERRORMSGS, NO_INFOMSGS ” -o \\Lgc004\SQLBackups\Process\DbccCheckDb\Error\LG0113DBCCCheckDB.log                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Paul Randal is a guide:

http://www.sqlskills.com/blogs/paul/how-to-get-all-the-corruption-messages-from-checkdb/

Challenged by the lack of budget and/or ill-place spending I’ve come to the conclusion a custom system is required for this diverse SQL Server environment.   In 2012 created a centralized database to manage the maintenance of all instances.  It is far less expensive than third-party tools.Heck getting fully licensed on some of those tools would be as expensive as my salary.

The system includes SQL Agent Jobs that collect statistics from each instance and records those in a centralized database.  Trying to keep locally created objects to a minimum only views are created in the master database under a defined schema so there is no confusion the source of the objects.  The individual dynamic management views present an overwhelming amount of information and frankly, I don’t understand all of it.

All the data collected is presented in SQL Server Reporting Services (SSRS) reports.  These reports are at the Enterprise level down to the database level.  The son_db database in the Elite system is very important so special daily reports are created  just for that server and others just as important.  Subscriptions are sent to me and others where the reports identify problems like out of space issues, missed back-ups, etc.

  1. Weekly Powershell script to scan the network for servers running SQL Server services
  2. Daily job to inventory instances
  3. Daily Job collecting maintenance information
  4. Daily Full Backups
  5. Daily Log Backups
  6. Weekly baseline collection

I’m looking forward to building this package out and possibly adding a front-end.  I’m not much of an application developer, so that may be more than a year away.