ronmichael

-- query all groups
SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''Group'' ')
	
-- query all users
SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, samAccountName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''User''  ') where samAccountName not like '%$'

-- to query ALL group memberships, you'll have to cursor through
-- all groups and get their members with a statement like this:
select * from OPENQUERY( ADSI, 'SELECT objectGuid, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''user'' and memberof=''CN=[group name],OU=[org unit],DC=mydomain,DC=local''  ') 
  

ronmichael

select
  s.name,
  stuff ( 
    ( SELECT ',' + c.name AS [text()] FROM cities c where c.stateid=s.id 
      FOR XML PATH('') ),
    1, 1, '') cities
from states s;

select
  s.name,

ronmichael

declare @id  int  		
declare @type	character(2) 		
declare	@pages	int			
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)

create table #spt_space

ronmichael

select top 100
qs.max_elapsed_time/1000000.00,
db.name db,
ob.name object,
substring(st.text,1,100) preview,
total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset

ronmichael

SELECT 
  DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
 ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
 ,SI.NAME AS IndexName
 ,DPS.INDEX_TYPE_DESC AS IndexType
 ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
 ,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 

ronmichael

-- in this case we're giving the cert permission to alter the schema
-- (maybe explicitly disably identify and insert something into an identity column)

-- set up cert with permission to alter schema...
CREATE MASTER KEY ENCRYPTION BY password = '**opensesame**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser

-- run or rerun the following line whenever you alter procedure,

ronmichael

SELECT
ex.UserName, ex.Format, 
cat.Path,
cat.Name, ex.Parameters, 
CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate,
ex.TimeStart, ex.TimeEnd, ex.TimeDataRetrieval, ex.TimeProcessing, ex.TimeRendering, ex.Status, 
ex.ByteCount, ex.[RowCount]
FROM ReportServer..ExecutionLog AS ex INNER JOIN
ReportServer..Catalog AS cat ON ex.ReportID = cat.ItemID
ORDER BY ex.TimeStart DESC

ronmichael

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

// just some rough playing around converting an object to RDF Turtle.

class ObjectToTurtle
{

ronmichael

# delete old SharePoint 2010 event receivers to eliminate duplicate workflow actions

function examineWeb($web) {

  $spLists = $web.Lists;

	for ($listnum=0; $listnum -lt $spLists.Count; $listnum+=1)
	{
		$($web.Title + " - " + $spList.Title)

ronmichael

select 
'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] 
        WITH PASSWORD=' + master.sys.fn_varbintohexstr(password_hash) + ' HASHED,
        SID = ' + master.sys.fn_varbintohexstr([sid]) + ',  
        DEFAULT_LANGUAGE=[us_english], 
        CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    ALTER LOGIN [' + [name] + ']