Difference between revisions of "SQL Server"

Revision as of 23:55, 17 June 2010

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

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)