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 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)