What permissions do you have in the Windows Domain?

Trying to figure what permissions I have in the domain

My friend gave me this tip
Go to Command Prompt

Type in Gpresult /S yoursystemname /Z /U DomainName\username

And you get the permissions and all your Group policies in the Domain.

For a brief description:
http://technet.microsoft.com/en-us/library/cc733160.aspx

Find out who is viewing the reports on SQL server reporting services 2005

This SQL script should give you the list of users using the reports on the reporting server 2005 platform.It can be used for auditing platform as well.

SELECT s.ReportName,s.UserName,COUNT(*)
FROM
(
SELECT cat.Name ReportName,
ex.UserName,
TimeStart
FROM ReportServer.dbo.ExecutionLog AS ex
INNER JOIN ReportServer.dbo.Catalog AS cat ON ex.ReportID = cat.ItemID
WHERE ex.TimeStart >= GETDATE() - 1
--ORDER BY ReportName
) s
GROUP BY s.ReportName,s.username

Playing With SQL Server Cache(DMV)

Yesterday Afternoon me and my friend were doing some serious Sql manipulation on some heavy datasets that needs to go inside the Cube.We have written some dynamic SQL statement that is going to insert some 40000 rows into a Control table for a Dimension.
Weird Enough my friend accidently closed the Query window and for a second we thought we lost it.

But Thanks to Dynamic Management views(DMV) from Sql server 2005.we were able to get the query back from the Cache..The query we used is as follows


select qs.usecounts, cacheobjtype, objtype, qt.text
from sys.dm_exec_cached_plans qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt
--where qt.text like '%%'
order by qt.text

and thats it we were able to get back the query...quite cool way of doing things..isnt it?

Missing Indexes on a table for Sql server 2005

The following consolidated Query from all the DMV's should give the missing indexes in the table


SELECT
statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO


When you create indexes , Please note the following
a. The DML operations will become slow if there are many indexes
b. More diskspace is required if you create more indexes..Also depends on the column datatype.
c. Create 100% fill factor on the indexes if the table is used only for read purposes; For all other DML operations balance the fillfactor between 0 and 100 percent.

Excel:Sorting Excel Sheets using VB Macro

Sub SortSheets()

Dim i As Integer, j As Integer
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move after:=Sheets(j + 1)
End If
Next j
Next i
End Sub

Cleaning Phone Numbers using CTE

Cleaning Phone Numbers using the Common Table Expressions

Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='(208)*/ 555-1212',
@Str2='';

With PhoneClean as
(
Select
 Case
  when SubString(@Str1,1,1) like '[0-9]' then SubString(@Str1,1,1)
  else ''
 End[Chr],
 1[Idx]
Union All
Select
 Case
  when SubString(@Str1,Idx+1,1) like '[0-9]' then SubString(@Str1,Idx+1,1)
  else ''
 End,
 Idx+1
from PhoneClean
where (Idx+1)<=Len(@Str1) ) Select  @Str2=@Str2+Chr from PhoneClean option (MaxRecursion 0); Select  @Str2; 

Source:www.sqlservercentral.com  

Getting the ForeignKeys Base and Reference Tables in a Database

select d.name as fk,b.name as base,c.name as ref from sysforeignkeys a, sysobjects b, sysobjects c,sysobjects d
where b.id=a.fkeyid
and c.id=a.rkeyid
and d.id=a.constid

Sending Email in SSIS without using the Sendmail Task

Using the System.Web.Mail To send an attachment along with the email

Imports System.Web.Mail

Public Sub Main()

Dim message As MailMessage = New System.Web.Mail.MailMessage()
Dim Att As String

Att = Dts.Variables("Attachments").Value.ToString
Att = Att & Now.Day.ToString & Now.Month.ToString & Now.Year.ToString & ".xls"

'MsgBox(Att.ToString)

message.From = ""
message.To = ""
message.Subject = "Abandoned Emails " & Now.Day.ToString & Now.Month.ToString & Now.Year.ToString

Dim oattach As MailAttachment = New MailAttachment(Att)
message.Attachments.Add(oattach)

SmtpMail.SmtpServer = ""
SmtpMail.Send(message)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Who receives the email when a job fails?

Here is a script that gives you the list of operators name who receive notifications when a job fails

SELECT sysoperators.name AS JobName, sysjobs.name AS OperatorName, sysjobs.enabled, sysoperators.id
FROM sysjobs INNER JOIN
sysoperators ON sysjobs.notify_email_operator_id = sysoperators.id


You need to change the sysjobs.notify_email_operator_id to change the Notifications of the Operator.