[T-SQL] SQL認證帳號新增資料庫未正常掛入使用者


工作的資料庫其中一個資料表超過 2 億 2 千萬筆,但這個資料表常常用在 JOIN 上,導致工作日查詢網頁時,大概要拖到 20 秒以上,假日沒人用倒還好,大概 3 秒可以出來。

某個資料表超過兩億筆
某個資料表超過兩億筆

這個資料表存了超過兩百個專案的資料,所以考慮採專案切割的方式,將每個專案資料切到不同資料庫,再由各專案資料庫的資料表進行 JOIN ,減少維度障礙。

超過兩百個專案進行切割,換句話說就是要建立超過兩百個資料庫,所以打算建立完整的 T-SQL 語法,方便後續使用。

正常建立的資料庫使用者如下,考慮到管理問題,預設加入 [BUILTIN\Administrators] ,再加入自行建立的 SQL 認證的 [馬賽克B] ,以下簡稱 UserB 。

資料庫的使用者
資料庫的使用者

開發的時候用測試機來測 T-SQL ,使用本機 Windows 認證,所使用的 T-SQL 語法簡化如下:

USE [master]
GO
/* 建立資料庫 */
GO
USE [專案資料庫]
GO
/****** User [BUILTIN\Administrators] ******/
CREATE USER [BUILTIN\Administrators] FOR LOGIN [BUILTIN\Administrators] WITH DEFAULT_SCHEMA=[dbo]
GO
/* BUILTIN\Administrators Set db_owner */
/*
EXEC sp_addrolemember 'db_owner','BUILTIN\Administrators' /* Before MSSQL 2008r2 */
*/
ALTER ROLE [db_owner] ADD MEMBER [BUILTIN\Administrators]
GO
/* --分隔線-- */
/****** User [*******B] ******/
CREATE USER [*******B] FOR LOGIN [*******B] WITH DEFAULT_SCHEMA=[dbo]
GO
/* [*******B] Set db_owner */
ALTER ROLE [db_owner] ADD MEMBER [*******B]
GO


正常建立完成的 UserB 點選 [屬性] 可看到 [使用者名稱]、[登入名稱] 都如同 UserB 的內容,左下角的輸出畫面可以看到沒有錯誤。

正常建立的 UserB 帳號
正常建立的 UserB 帳號

測試完沒問題後,從我的電腦跑,變成遠端存取建立資料庫,但這一建立就發生錯誤。

遠端建立無法正常加入使用者
遠端建立無法正常加入使用者

起初發現是 UserB 無法加入這個資料庫,但用手動也加入不了,經詳細比對後,發現是 SQL Server 自動掛入的 dbo 帳號會變成:

[使用者名稱]=dbo,但 [登入名稱] =UserB ,然後 [登入名稱] 又不給我改回 dbo ,所以無法加入 UserB,只好砍掉重練。

經過多次測試,發現上面的 T-SQL 語法從分隔線處分成兩次執行,第一次執行時,SQL Server 會正常掛入 dbo 帳號,第二次執行就可以正常掛入 UserB 帳號。

目前要得到我要的資料庫安全性登入帳號的測試結論是:

  1. 本機使用 Windows 認證時,上面 T-SQL 語法可以同次執行
  2. 遠端使用 SQL Server 認證時,上面的 T-SQL 語法需要從分隔線處分兩次執行

原因我還沒找到,做個心得記錄,後面可以翻查。

本機 Windows 認證我測試過 SQL Server 2008r2、2012、2014、2016、2017 都正常。

Categories: SQL, 工作點滴, 技術分享 | 標籤: | 發表留言

文章分頁導航

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

在WordPress.com寫網誌.

%d 位部落客按了讚: