Difference between revisions of "SQL Server"

Revision as of 20:43, 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)