Win 2008 Run As

Create a batch file that has the following on SQL server:

cd\
cd C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
runas /user:csodmgmt\palexander ssms

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

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

Format drives with correct allocation and offset for maximum SQL Server performance

Format drives with correct allocation and offset for maximum SQL Server performance

Setup Database Mail to use a Gmail, Hotmail, Yahoo or AOL account

Setup Database Mail to use a Gmail, Hotmail, Yahoo or AOL account

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL « SQL Server Journey with SQLAuthority

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL « SQL Server Journey with SQLAuthority

How do I get a list of SQL Server tables and their row counts?

How do I get a list of SQL Server tables and their row counts?

SQL Server Column Level Encryption Example using Symmetric Keys

SQL Server Column Level Encryption Example using Symmetric Keys

SQL Server – Performance – Best Practice – Create Index Before or After a data insert? - Nakul's Blog

SQL Server – Performance – Best Practice – Create Index Before or After a data insert? - Nakul's Blog

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