SQL Script to find all Date/time columns in database

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

SQL Server SSRS: List out all Data Sources and all their dependency objects/Reports

 This Script will list all the data sources by their actual name, and all their dependent items:

SELECT C2.NAME AS Data_Source_Name
 ,C.NAME AS Dependent_Item_Name
 ,C.Path AS Dependent_Item_Path
FROM ReportServer.dbo.DataSource AS DS
INNER JOIN ReportServer.dbo.CATALOG AS C ON DS.ItemID = C.ItemID
 AND DS.Link IN (
  SELECT ItemID
  FROM ReportServer.dbo.CATALOG
  WHERE Type = 5
  ) --Type 5 identifies data sources
FULL JOIN ReportServer.dbo.CATALOG C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5
ORDER BY C2.NAME ASC
 ,C.NAME ASC;

Grant/Revoke Permissions to run SQL Server Profiler for a non System Admin User

Security and Permissions

Tracing can expose a lot of information about not only the state of the server, but also the data sent to and returned from the database engine by users. The ability to monitor individual queries down to the batch or even query plan level is at once both powerful and worrisome; even exposure of stored procedure input arguments can give an attacker a lot of information about the data in your database.
In order to protect SQL Trace from users that should not be able to view the data it exposes, previous versions of SQL Server allowed only administrative users (members of the sysadmin fixed server role) access to start traces. That restriction proved a bit too inflexible for many development teams, and as a result it has been loosened.



GRANT/REVOKE ALTER TRACE permission using T-SQL

 We can also assign this permission by running T-SQL commands. We can get this privilege by running the below commands in the Master database. If you want to assign this permission by running this T-SQL command then do not follow Step 3 thru Step 5 above.

--  Grant access to Windows Login
USE Master;
GO
GRANT ALTER TRACE TO [DomainName\WindowsLogin]
GO

-- To Grant access to a SQL Login
USE master;
GO
GRANT ALTER TRACE TO Geoff;
GO

GRANT permission on ALTER trace by T-SQL
Now you can go ahead and launch SQL Server Profiler to verify that you have access.

  If you want to revoke this access from the assigned login then you can run the below commands to remove the ALTER TRACE permission.

--REVOKE access FROM a SQL Login
USE Master;
GO
REVOKE ALTER TRACE FROM Geoff;
GO

-- REVOKE access FROM a Windows Login
USE master;
GO
REVOKE ALTER TRACE FROM [DomainNAME\WindowsLogin]
GO

Delete Statement running forever/slow in SQL Server

Things that can cause a delete to be slow:

• cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
• Transaction log needing to grow
• Many Foreign keys to check
• deleting a lot of records
• many indexes
• deadlocks and blocking
• triggers

AES algorith is stronger algorthm than 3DES/Triple-DES

AES is an algorithm adopted as a Federal U.S. government standard in 2002 and approved
by the NSA. It is a stronger algorithm than Triple-DES. When you choose the algorithm,
you need to balance between security and performances. AES_128 is gradually becoming
more vulnerable as new attacks are discovered but it is still considered reasonably safe. If your database contains classified
information, you should go for a bigger key, which is harder to crack. But the bigger the
key, the higher the performance impact will be. This being said, the performance impact
of TDE is relatively low.

Reference: Microsoft SQL Server 2012 Security Cookbook