List all Orphan users in a SQL Server database

CREATE TABLE ##ORPHANUSER (
DBNAME VARCHAR(100)
,USERNAME VARCHAR(100)
,CREATEDATE VARCHAR(100)
,USERTYPE VARCHAR(100)
)
EXEC SP_MSFOREACHDB ' USE [?]
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE 
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN''
WHEN ISNTGROUP = 1 THEN ''NT GROUP''
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN''
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins)'
SELECT *
FROM ##ORPHANUSER
DROP TABLE ##ORPHANUSER

List tables on their dependency order based on foreign keys for Data Migration

If you want to perform bulk insert operation on multiple tables.

The tables have foreign key relationships between themselves.

If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.



Requirement



Produce a list of tables within a database ordered according to their dependencies.

Tables with no dependencies (no foreign keys) will be 1st.

Tables with dependencies only in the 1st set of tables will be 2nd.

Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.

and so on...





WITH cte (
 lvl
 ,object_id
 ,name
 ,schema_Name
 )
AS (
 SELECT 1
  ,object_id
  ,sys.tables.name
  ,sys.schemas.name AS schema_Name
 FROM sys.tables
 INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE type_desc = 'USER_TABLE'
  AND is_ms_shipped = 0
 
 UNION ALL
 
 SELECT cte.lvl + 1
  ,t.object_id
  ,t.name
  ,S.name AS schema_Name
 FROM cte
 JOIN sys.tables AS t ON EXISTS (
   SELECT NULL
   FROM sys.foreign_keys AS fk
   WHERE fk.parent_object_id = t.object_id
    AND fk.referenced_object_id = cte.object_id
   )
 JOIN sys.schemas AS S ON t.schema_id = S.schema_id
  AND t.object_id <> cte.object_id
  AND cte.lvl < 30
 WHERE t.type_desc = 'USER_TABLE'
  AND t.is_ms_shipped = 0
 )
SELECT schema_Name
 ,name
 ,MAX(lvl) AS dependency_level
FROM cte
WHERE schema_Name LIKE '%ORCA%'
GROUP BY schema_Name
 ,name
ORDER BY dependency_level
 ,schema_Name
 ,name;



Reference

List out all MDF and LDF File locations of SQL Sever Databases

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

List of all user databases in SQL Server

SELECT *
FROM   master.sys.databases
WHERE  Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0

Query to List all the partitioned tables in SQL Server database

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]

List of Permissions and their Description in SQL Server

Permission name	Description
ALTER	Permission to modify the object's definition
CONNECT	Permission to access the database or connect to the endpoint
DELETE	Permission to delete the object
EXECUTE	Permission to execute the stored procedure or the function
IMPERSONATE	Permission to take the identity of a principal, by the means of an EXECUTE AS command
INSERT	Permission to insert data into the table or view
REFERENCES	Permission to reference the object in a foreign key definition, or to declare a view or function WITH SCHEMABINDING referencing the object
SELECT	Permission to issue a SELECT command against the object or column
TAKE OWNERSHIP	Permission to become the owner of the object
UPDATE	Permission to update the data
VIEW DEFINITION	Permission to view the definition (structure) of the object

Script to obtain SQL feature list from the command line

The components for SQL Server 2012 are divided by server and then management tools. You can get the server level components by using a PowerShell command like:

Get - Service * SQL 

To get the management tools would require either registry search for the uninstall list or you can query the WMI class win32_product:

get - wmiobject win32_product |
WHERE {$_.NAME - match "SQL" -
 AND $_. vendor - eq "Microsoft Corporation" } |

SELECT NAME
 ,version

Reference:  http://dba.stackexchange.com/questions/77016/how-to-obtain-sql-feature-list-from-the-command-line

Script to list out all recently ran queries in SQL Server

SELECT deqs.last_execution_time AS [Time]
 ,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Script to list out all object level permissions in SQL Server

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) + CASE 
  WHEN cl.column_id IS NULL
   THEN SPACE(0)
  ELSE '(' + QUOTENAME(cl.NAME) + ')'
  END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Object Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
 AND cl.[object_id] = PERM.major_id
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Database Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE PERM.major_id = 0
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

List of services started automatically by Windows that you could consider stopping

# DHCP Client: On most servers, IP addresses are fixed and DHCP is not needed.

#DNS Client: This caches DNS names locally.

#Network Location Awareness: This collects and stores the configuration information

for the network and notifies the programs when this information is modified. You can't

disable it unless you use the Windows 2008 Advanced Firewall.

#Print Spooler: This loads the files to the memory for printing later. It can be disabled

if you don't print from the server.

#Windows Error Reporting Service: This allows errors to be reported when programs

stop working or responding, and allows existing solutions to be delivered. It can safely

be disabled.

#Windows Firewall: You can disable this service, if you are using a network firewall.

#Shell Hardware Detection: This activates AutoPlay for removable devices.

On Windows Server 2008 R2, this service stops when nobody is logged in,

to minimize risks. You can disable it.

Reference: Microsoft SQL Server 2012 Security

Cookbook

Script to list data types of all columns in a Database in SQL Server

SQL Server tables displays the data type of
each column inside the table. You can do this dozens of ways, but a popular method shown in
the following example joins the sys.objects table with the sys.columns table. There are two
functions that you may not be familiar with in the following code. The TYPE_NAME() function
translates the data type id into its proper name. To go the opposite direction, you could use the
TYPE_ID() function. The other function of note is SCHEMA_ID(), which is used to return the
identity value for the schema. This is useful primarily when you want to write reports against the
SQL Server metadata.

USE AdventureWorks
GO

SELECT o.NAME AS ObjectName
 ,c.NAME AS ColumnName
 ,TYPE_NAME(c.user_type_id) AS DataType
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.NAME = 'Department'
 AND o.Schema_ID = SCHEMA_ID('HumanResources')

Script to list out all User Stored Procedures in database

SELECT NAME
 ,type
FROM dbo.sysobjects
WHERE (type = 'P')
ORDER BY NAME