Tuesday, January 17, 2012

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Wednesday, January 11, 2012

Active Directory from SQL

Configuration on Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'[10.10.10.10]'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\user',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO



select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
') AS tblADSI
ORDER BY SAMAccountName


select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName,displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND sAMAccountName = ''username''
') AS tblADSI
ORDER BY displayname




Find groups and users all together



-- Configure AD Domain to search

DECLARE @strDC NVARCHAR(100)
SET @strDC = 'dc=csodmgmt,dc=corp'

-- Declare Variables

DECLARE @chvAlphaChars VARCHAR(60),
@chvSearch VARCHAR(10),
@chvSearchLevel1 VARCHAR(1),
@chvSearchLevel2 VARCHAR(1),
@chvSearchLevel3 VARCHAR(1),
@intcountLevel1 INT,
@intcountLevel2 INT,
@intcountLevel3 INT,
@intRowCount INT,
@strSQL NVARCHAR(4000),
@strADSISQL NVARCHAR(4000),
@Login VARBINARY(85),
@CN VARCHAR(512),
@CT INT,
@ADRoot VARCHAR(255)


-- Declare temp tables

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Users'))
DROP TABLE #LDAP_AD_V2_USERS

CREATE TABLE [DBO].[#LDAP_AD_V2_USERS] (
[ROW_ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[SID] [VARBINARY](85) NULL,
[SAMACCOUNTNAME] [NVARCHAR](256) NULL,
[CN] [NVARCHAR](256) NULL,
[SN] [NVARCHAR](256) NULL,
[DISPLAYNAME] [NVARCHAR](256) NULL,
[GIVENNAME] [NVARCHAR](256) NULL,
[TELEPHONENUMBER] [NVARCHAR](256) NULL,
[ADSPATH] [NVARCHAR](256) NULL,
[HOMEDIRECTORY] [NVARCHAR](256) NULL,
[MAIL] [NVARCHAR](256) NULL,
[MEMBEROF] [NVARCHAR](256) NULL,
[PRIMARYGROUPID] [INT],
[CREATETIMESTAMP] DATETIME)
ON [PRIMARY]

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Groups'))
DROP TABLE #LDAP_AD_V2_GROUPS

CREATE TABLE [DBO].[#LDAP_AD_V2_GROUPS] (
[ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[LOGIN] [VARCHAR](512) NULL,
[EMAIL] [VARCHAR](255) NULL,
[ACCTNAME] [VARCHAR](512) NULL,
[DISTNAME] [VARCHAR](512) NULL,
[CREATEDDATE] [DATETIME] NULL,
[CHANGEDDATE] [DATETIME] NULL,
[MGR] [VARCHAR](512) NULL,
[SID] [VARBINARY](85) NULL)
ON [PRIMARY]

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_UserGroups'))
DROP TABLE #LDAP_AD_V2_USERGROUPS

CREATE TABLE [DBO].[#LDAP_AD_V2_USERGROUPS] (
[USERLOGIN] [VARBINARY](85) NOT NULL,
[GROUPLOGIN] [VARBINARY](85) NULL)
ON [PRIMARY]

-- POPULATION OF USERS

-- Search letters to cycle through
-- any chars, but the first char must be a space
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'
SET NOCOUNT ON

-- start on non space char
SET @intcountLevel1 = 2
-- first level loop
WHILE @intcountLevel1 <= LEN(@chvAlphaChars)
BEGIN
-- get first level char
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
-- reset start on space
SET @intcountLevel2 = 1
-- second level loop
WHILE @intcountLevel2 <= LEN(@chvAlphaChars)
BEGIN
-- reset start on space
SET @intcountLevel3 = 1
-- third level loop
WHILE @intcountLevel3 <= LEN(@chvAlphaChars)
BEGIN
-- setup the string to search for. By using the trim function we can form each level depending on no records
-- eg A 99, B 1000 > BA 9, BB 20 etc
-- trim the spaces forming just A, B, C ; AA, AB for search etc
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
SET @strADSISQL = 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'where objectCategory = ''''Person'''' '
+ CHAR(13) + 'and objectClass = ''''user'''' '
+ CHAR(13) + 'and sAMAccountName = ''''' + @chvSearch + '*'''' '
SET @strSQL = 'insert into #LDAP_AD_V2_Users (sid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp) '
+ CHAR(13) + 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'

EXEC SP_EXECUTESQL @strSQL

SET @intRowCount = @@ROWCOUNT

-- prints what string is being searched for : no of inserts
-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)

-- if searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
IF @intRowCount < 1000
AND @chvSearchLevel2 = ''
SET @intcountLevel2 = @intcountLevel2 + 100

-- if searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
IF @intRowCount < 1000
AND @chvSearchLevel3 = ''
SET @intcountLevel3 = @intcountLevel3 + 100

-- else over 1000 so increment third level
SET @intcountLevel3 = @intcountLevel3 + 1
END

-- increment next second level char
SET @intcountLevel2 = @intcountLevel2 + 1
END

-- increment next first level char
SET @intcountLevel1 = @intcountLevel1 + 1
END


-- POPULATION OF GROUPS

SET @strADSISQL = 'select objectSid, managedBy, whenChanged, whenCreated, distinguishedName, name, samAccountName, mail '
+ CHAR(13) + 'FROM ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'WHERE objectCategory = ''''Group'''' '

SET @strSQL = 'insert into #LDAP_AD_V2_Groups (sid,mgr,changeddate,createddate,distname,acctname,email,login) '
+ CHAR(13) + 'select objectSid,managedBy,whenChanged,whenCreated,distinguishedName,name,mail,samAccountName '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'

-- PRINT @strSQL
EXEC SP_EXECUTESQL @strSQL


-- POPULATION OF USER > GROUPS RELATIONSHIP TABLE

IF EXISTS (SELECT *
FROM TEMPDB.DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('tempdb.dbo.#CT'))
DROP TABLE #CT

-- Create a temporary table to hold AD user SID values
CREATE TABLE #CT (CT VARBINARY(85))

-- Declare and open a cursor to step through the list of Active Directory groups
DECLARE CURGROUPS CURSOR FOR
SELECT SID,
DISTNAME
FROM #LDAP_AD_V2_GROUPS
ORDER BY LOGIN

OPEN CURGROUPS

FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN

WHILE @@FETCH_STATUS = 0
BEGIN
-- Empty the temp table
TRUNCATE TABLE #CT

-- Build a SQL statement to insert the SID values directly from the linked server into the temp table
SET @strsql = 'INSERT #CT
SELECT *
FROM OPENQUERY (
ADSI,
''SELECT objectSid
FROM ''''LDAP://' + @strDC + '''''
WHERE objectCategory = ''''User''''
AND memberof=''''' + REPLACE(@CN,'''','''''''''') + ''''''')'

EXEC( @strsql)

-- Select the number of records inserted. If this value is less than 1000 then there is no need
-- to execute the OLE calls, and we simply copy the values into the correlation table.
SELECT @CT = COUNT(* )
FROM #CT

--PRINT @ct
--PRINT @strsql

IF @CT <> 0
BEGIN
INSERT #LDAP_AD_V2_USERGROUPS
(USERLOGIN,
GROUPLOGIN)
SELECT CT,
@Login
FROM #CT
WHERE CT IS NOT NULL
END

FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN
END

CURSORERROR:
CLOSE CURGROUPS
DEALLOCATE CURGROUPS
DROP TABLE #CT

/*
-- Individual SQL Statements to see users, groups & relationships
select * from #LDAP_AD_V2_Users
select * from #LDAP_AD_V2_Groups
select * from #LDAP_AD_V2_UserGroups
*/

-- Final Query to tie together the 3 tables and produce a report of group membership

SELECT #LDAP_AD_V2_GROUPS.ACCTNAME AS GROUPNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME AS USERACCOUNT
FROM #LDAP_AD_V2_USERGROUPS
INNER JOIN #LDAP_AD_V2_USERS
ON #LDAP_AD_V2_USERGROUPS.USERLOGIN = #LDAP_AD_V2_USERS.SID
INNER JOIN #LDAP_AD_V2_GROUPS
ON #LDAP_AD_V2_USERGROUPS.GROUPLOGIN = #LDAP_AD_V2_GROUPS.SID
where #LDAP_AD_V2_USERS.SAMACCOUNTNAME = ' username '
ORDER BY #LDAP_AD_V2_GROUPS.ACCTNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME

Monday, October 24, 2011

sp_helpindex3

CREATE procedure [dbo].[sp_helpindex3] @objname nvarchar(776) -- the table to check for indexes as -- PRELIM set nocount on declare @objid int, -- the object id of the table @indid smallint, -- the index id of an index @groupid int, -- the filegroup id of an index @indname sysname, @groupname sysname, @status int, @keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3) @inc_columns nvarchar(max), @inc_Count smallint, @loop_inc_Count smallint, @dbname sysname, @ignore_dup_key bit, @is_unique bit, @is_hypothetical bit, @is_primary_key bit, @is_unique_key bit, @auto_created bit, @no_recompute bit, @filter_definition nvarchar(max) -- Check to see that the object names are local to the current database. select @dbname = parsename(@objname,3) if @dbname is null select @dbname = db_name() else if @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end -- Check to see the the table exists and initialize @objid. select @objid = object_id(@objname) if @objid is NULL begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196) declare ms_crs_ind cursor local static for select i.index_id, i.data_space_id, i.name, i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint, s.auto_created, s.no_recompute, i.filter_definition from sys.indexes i join sys.stats s on i.object_id = s.object_id and i.index_id = s.stats_id where i.object_id = @objid open ms_crs_ind fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition -- IF NO INDEX, QUIT if @@fetch_status < 0 begin deallocate ms_crs_ind raiserror(15472,-1,-1,@objname) -- Object does not have any indexes. return (0) end -- create temp tables CREATE TABLE #spindtab ( index_name sysname collate database_default NOT NULL, index_id int, ignore_dup_key bit, is_unique bit, is_hypothetical bit, is_primary_key bit, is_unique_key bit, auto_created bit, no_recompute bit, groupname sysname collate database_default NULL, index_keys nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr filter_definition nvarchar(max), inc_Count smallint, inc_columns nvarchar(max) ) CREATE TABLE #IncludedColumns ( RowNumber smallint, [Name] nvarchar(128) ) -- Now check out each index, figure out its type and keys and -- save the info in a temporary table that we'll print out at the end. while @@fetch_status >= 0 begin -- First we'll figure out what the keys are. declare @i int, @thiskey nvarchar(131) -- 128+3 select @keys = index_col(@objname, @indid, 1), @i = 2 if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1) select @keys = @keys + '(-)' select @thiskey = index_col(@objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' while (@thiskey is not null ) begin select @keys = @keys + ', ' + @thiskey, @i = @i + 1 select @thiskey = index_col(@objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' end -- Second, we'll figure out what the included columns are. SELECT @inc_Count = count(*) FROM sys.tables AS tbl INNER JOIN sys.indexes AS si ON (si.index_id > 0 and si.is_hypothetical = 0) AND (si.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id) INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1 and (si.index_id = @indid) and (tbl.object_id= @objid) IF @inc_Count > 0 INSERT #IncludedColumns SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id) , clmns.name FROM sys.tables AS tbl INNER JOIN sys.indexes AS si ON (si.index_id > 0 and si.is_hypothetical = 0) AND (si.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id) INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1 and (si.index_id = @indid) and (tbl.object_id= @objid) SELECT @inc_columns = [Name] FROM #IncludedColumns WHERE RowNumber = 1 SET @loop_inc_Count = 1 WHILE @loop_inc_Count < @inc_Count BEGIN SELECT @inc_columns = @inc_columns + ', ' + [Name] FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1 SET @loop_inc_Count = @loop_inc_Count + 1 END select @groupname = null select @groupname = name from sys.data_spaces where data_space_id = @groupid -- INSERT ROW FOR INDEX insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys, @filter_definition, @inc_Count, @inc_columns) -- Next index fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition end deallocate ms_crs_ind -- DISPLAY THE RESULTS select 'index_name' = index_name, 'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group case when index_id = 1 then 'clustered' else 'nonclustered' end + case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end + case when is_unique <>0 then ', unique' else '' end + case when is_hypothetical <>0 then ', hypothetical' else '' end + case when is_primary_key <>0 then ', primary key' else '' end + case when is_unique_key <>0 then ', unique key' else '' end + case when auto_created <>0 then ', auto create' else '' end + case when no_recompute <>0 then ', stats no recompute' else '' end + ' located on ' + groupname), 'index_keys' = index_keys, --'num_included_columns' = inc_Count, 'included_columns' = inc_columns, 'filter_definition' = filter_definition, 'index_id' = index_id from #spindtab order by index_name return (0) -- sp_helpindexwinc2 GO

Standardize SQL Server Installations with Configuration Files

Standardize SQL Server Installations with Configuration Files