Difference between revisions of "SQL Server"

Revision as of 20:42, 1 May 2017

• 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;

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)