SQL Server

• Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication

Count database connections in database

SELECT DB_NAME(sP.dbid) AS the_database
       , COUNT(sP.spid) AS total_database_connections
FROM sys.sysprocesses sP
GROUP BY DB_NAME(sP.dbid)
ORDER BY 1;

List non-clustered indexes in database

 select t.name as tablename, i.* 
 from sys.indexes i, sys.tables t
 where i.object_id = t.object_id
  and i.type_desc = 'NONCLUSTERED'

List unused indexes in database

SELECT   DB_NAME() AS DATABASENAME, 
         OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, 
         B.NAME AS INDEXNAME, 
         B.INDEX_ID 
FROM     SYS.OBJECTS A 
         INNER JOIN SYS.INDEXES B 
           ON A.OBJECT_ID = B.OBJECT_ID 
WHERE    NOT EXISTS (SELECT * 
                     FROM   SYS.DM_DB_INDEX_USAGE_STATS C 
                     WHERE  B.OBJECT_ID = C.OBJECT_ID 
                            AND B.INDEX_ID = C.INDEX_ID) 
         AND A.TYPE <> 'S' 
ORDER BY 1, 2, 3

Identify 'missing' indexes

 SELECT
    CONVERT(decimal(28, 1), migs.avg_total_user_cost * migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(varchar, mig.index_group_handle) +
    '_' + CONVERT(varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' +
    ISNULL(mid.equality_columns, '') +
    CASE WHEN mid.equality_columns IS NOT NULL AND
              mid.inequality_columns IS NOT NULL THEN ','
         ELSE ''
    END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' +
                                                            mid.included_columns +
                                                            ')', '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id],
    mig.index_group_handle,
    mid.index_handle
 FROM
    sys.dm_db_missing_index_groups mig INNER JOIN 
    sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN
    sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
 WHERE
    CONVERT(decimal(28, 1), migs.avg_total_user_cost * migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans)) > 10 AND
    database_id = DB_ID()
 ORDER BY
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks +
                                                       migs.user_scans) DESC

List permissions each table in a database

Use the sp_helprotect system stored proc. Run with no arguments it list permissions on all database objects

List all tables in a database

Use databasename
SELECT * from sys.tables

Run a command on each table in a database

Use the sp_msforeachtable undocumented system stored proc, for example:

sp_msforeachTable @command1='select count(*) NR from ? 
  where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar''
'

To restrict tables to those in the dbo schema, use the @whereAnd parameter:

sp_msforeachTable @command1='select count(*) NR from ? 
  where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar''
' ,
  @whereAnd = 'and uid = 1'

SQL 2008 - Change sysadmin password on local database

Pre-requisite: Make sure your Domain login is a member of the PC’s local admin group.

1) Stop SQL Server (either from Management Studio or Services)

2) Open a cmd prompt at your sql installation folder (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn) and run: sqlserv.exe –m to put it into single user mode. Leave the command window open.

3) Open another cmd prompt (pathed) and run sqlcmd –E –S .

  CREATE LOGIN [BEACON\myUsername] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
  EXEC master..sp_addsrvrolemember @loginame = N'BEACON\myUsername', @rolename = N'sysadmin'

4) Close the both command windows and restart the SQL Service

Open SS Management Studio, expand server roles and check you are in the sysadmin role.

Create AdventureWorks Sample Cube for 2008/R2 Analysis Services

• Installing Analysis Services Database (CodePlex)