@manhnguyenv

Welcome to my blog!

Default SA password of SQL Server

December 8, 2018 09:47

Default SA password of SQL Server (edit)

Here are the steps I did to fix this:

  • Login to the SQL Server using a local account which has administrative privileges account (eg: .\Administrator)
  • Once you have logged into Windows, open SQL Management Studio
  • Connect to the SharePoint SQL instance using Windows Authentication
  • Enable Mixed Mode Authentication (this is not enabled by default for the SharePoint SQL instance)
  • Set a password for the sa account
  • Enable the sa account
  • Restart the SQL instance (required due to change in authentication mode)

Enabling Mixed Mode Authentication:

  1. Right click on the SQL server instance
  2. Click on Properties
  3. Click on Security on the left pane
  4. Click on SQL Server and Windows Authentication Mode under the Server authentication section

You can also use the following SQL query to do the same:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

[Note: 2 indicates mixed mode authentication. 1 is for windows only authentication]

Setting a password on the sa account:

  1. Under the SQL instance, expand Security and Logins
  2. Right click on sa and click on Properties
  3. Type the new password in the Password and Confirm Password boxes

You can also use the following SQL query to do the same:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF

[Note: CHECK_POLICY=OFF ensures that Windows password policies of the computer on which SQL Server is running will NOT be enforced on this login]

Enabling the sa account:

  1. Under the SQL instance, expand Security and Logins
  2. Right click on sa and click on Properties
  3. Click on Status on the left pane
  4. Click on Enabled under the Login section

You can also use the following SQL query to do the same:

ALTER LOGIN [sa] ENABLE

Question

How to change a password for the `sa` user in MS SQL?

Answer

This can be done using the osql utility in a command prompt or using Microsoft SQL Management Studio.

1. Using the command prompt:

  • For a local MS SQL server use the following command: 

    osql -E -Q "exec sp_password NULL,'new_password','sa'"

  • For a remote MS SQL server use the following command: 

    osql -S servername_or_address -E -Q "exec sp_password NULL,'new_password','sa'"

2. Using Microsoft SQL Management Studio:

  • Open Microsoft SQL Management Studio. It can be done from the command line by executing ssms.exe .
  • Login into Microsoft SQL Management Studio with Administrator user credentials.
  • Go to Security > Logins > sa > properties .
  • Change the password.

Note: These actions are possible for Administrator account only.

If Windows administrator does not have permissions to reset 'sa' user password, follow instructions from the article: Connect to SQL Server When System Administrators Are Locked Out

Sql View Info

July 1, 2018 10:13

Sql View Info (edit)

Lấy thông tin Description của một cột trong SQL Server

select 
    st.name [Table],
    sc.name [Column],
    sep.value [Description]
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
                                        and sc.column_id = sep.minor_id
                                        and sep.name = 'MS_Description'
where st.name = 'T_Fighting'
and sc.name = 'Status'

Lấy thông tin về Table, Column, DataType trong SQL Server

SELECT TAB.NAME AS TableName, TAB.object_id AS ObjectID, COL.NAME AS ColumnName, TYP.NAME AS DataTypeName, TYP.max_length AS MaxLength
FROM   sys.columns COL
       INNER JOIN sys.tables TAB ON COL.object_id = TAB.object_id
       INNER JOIN sys.types TYP ON TYP.user_type_id = COL.user_type_id
where TAB.name = 'T_Fighting'

--where TYP.name = 'varchar'
--where COL.name = 'Status'
--where TAB.name = 'T_Fighting'

-- where TAB.name = ''
-- Uncomment above line and add to fetch details for particular table 

-- where COL.name = '' 
-- Uncomment above line and add to fetch details for particular column names 

-- where TYP.name = '' 
-- Uncomment above line and add to fetch details for particular Data Type

MySql + Sql Server

January 30, 2018 23:06

SQL Hints: http://sqlhints.com

Red-Gate: https://www.red-gate.com/simple-talk/sql/

SQL Blog: http://sqlblog.com/

SQL Server Central: http://www.sqlservercentral.com/

MySQL Tutorial: http://www.mysqltutorial.org/

Recursive sum in tree structure
https://stackoverflow.com/questions/24394601/recursive-sum-in-tree-structure

Finding a string value in a SQL Server table
https://www.mssqltips.com/sqlservertip/1654/finding-a-string-value-in-a-sql-server-table/

Concatenating Row Values in Transact-SQL
https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Exploring Your SQL Server Databases with T-SQL
https://www.red-gate.com/simple-talk/sql/database-administration/exploring-your-sql-server-databases-with-t-sql/

Questions About CUBE, ROLLUP and GROUPING SETs That You Were Too Shy to Ask
https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-cube-rollup-and-grouping-sets-that-you-were-too-shy-to-ask/

Hierarchy Data SQL Server
https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server

Hierarchy SQL Server
https://stackoverflow.com/questions/764497/flatten-adjacency-list-hierarchy-to-a-list-of-all-paths

What are the options for storing hierarchical data in a relational database?
https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database

Focusing on the SQL-standard, hierarchical and temporal data, and open source software
http://troels.arvin.dk/db/rdbms/links/#hierarchical

HIERARCHICAL DATA IN SQL
https://blog.duncanworthy.me/sql/hierarchical-data-in-sql/
https://blog.duncanworthy.me/sql/hierarchical-data-pt1-adjacency-list/
https://blog.duncanworthy.me/sql/hierarchical-data-pt2-adjacency-recursive-queries/
https://blog.duncanworthy.me/sql/hierarchical-data-pt3-displaying-the-tree/

Modeling an Adjacency List Hierarchy with SQL Server 2017 Graph Database
http://sqlblog.com/blogs/john_paul_cook/archive/2017/06/20/modeling-an-adjacency-list-hierarchy-with-sql-server-2017-graph-database.aspx

The Performance of Traversing a SQL Hierarchy
https://www.red-gate.com/simple-talk/sql/performance/the-performance-of-traversing-a-sql-hierarchy/

Hierarchies on Steroids
http://www.sqlservercentral.com/articles/Hierarchy/94040/

Managing Hierarchical Data in MySQL
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

  • Joe Celko's Trees and Hierarchies in SQL for Smarties, Second Edition (The Morgan Kaufmann Series in Data Management Systems) 2nd Edition
  • Murach's MySQL, 2nd Edition 2nd Edition

Managing Hierarchical Data in MySQL Using the Adjacency List Model
http://www.mysqltutorial.org/mysql-adjacency-list-tree/

How to create a MySQL hierarchical recursive query
https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

How to do the Recursive SELECT query in MySQL?
https://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql

Recursive self joins
https://dba.stackexchange.com/questions/46127/recursive-self-joins

Hierarchical queries in MySQL
https://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets/
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/swinging-from-tree-to-tree-using-ctes-part-1-adjacency-to-nested-sets.aspx

HierarchyID Aggregate Functions in T-SQL
https://stackoverflow.com/questions/8901427/hierarchyid-aggregate-functions-in-t-sql

SQL Server

August 2, 2017 13:15

Tìm hiểu về SCOPE_IDENTITY() trong MS SQL Server

Mấy cái này tài liệu đầy trên mạng, search google cái ra liền, hồi trước tui cũng có coi rồi mà cái tự nhiên nay xài lại quên mất tiêu nên sau khi xài xong ghi lại đây để mai mốt có quên nữa thì coi cho dễ:

@@IDENTITY: biến toàn cục, nếu trong table vừa Insert vào có trigger thì nó sẽ trả về identity của bên table mà trigger tác động chứ không phải table bạn vừa Insert nhé.

Ví dụ bạn thêm 1 bản ghi vào bảng Order, và có 1 trigger âm thầm thêm 1 bản ghi vào bảng OrderHistory, thì nếu dùng @@IDENTITY, bạn sẽ nhận được giá trị cuối cùng của cột Identity của bảng OrderHistory, nếu dùng SCOPE_IDENTITY, bạn sẽ nhận được giá trị cuối cùng của cột Identity của bảng Order.

Vụ này không để ý kỹ lộn hoài!

SCOPE_IDENTITY(): hàm có sẵn, trả về identity của table vừa Insert vào.

IDENT_CURRENT(‘table_name’): hàm có sẵn, cũng giống như SCOPE_IDENTITY() nhưng khác là không phụ thuộc vào session đang kết nối đến SQL Server.

Tối ưu hóa C# và SQL Server

1) Nạp dữ liệu – DataSet và DataReader

DataReader nhanh hơn gấp 2 lần.

2) Thuộc tính CommandType của đối tượng Command

  • CommandType.Text
  • CommandType.StoredProcedure
  • CommandType.TableDirect

Nhanh nhất là TableDirect, nhưng nên dùng StoredProcedure trong bất kỳ hoàn cảnh nào.

3) Chỉ nạp những cột cần thiết

4) Tìm kiếm dữ liệu – DataTable và DataView

DataView nhanh hơn gấp 1.5 lần.

5) Lọc dữ liệu – DataTable và DataView

DataView nhanh hơn gấp 100 lần.

Tham khảo: https://thuonglv.wordpress.com/2014/05/18/c-toi-uu-hoa-chuong-trinh-c-co-so-du-lieu/

Categories

Recent posts