使用者的 Default Schema 與權限設定
前一陣子遇到一個狀況，因為系統串接會讓兩個不同廠商的A、B 兩個系統，會去共用讀取同一個資料表。原本這樣的使用相安無事，但是後來因為 A 廠商在資料處理上需要使用 Unicode , 但 B 系統還是需要使用的資料型態，加上廠商也不願意去修改程式，造成一些困擾。
為了解決這樣的問題，所以就先使用 SQL Trace 攔一下 SQL 指令，發現兩個系統都是使用 select * from {table} 的指令去處理，因此就想到用 schema 的方式來騙過去。我模擬一下目前的做法，在系統中會有一個類似 DEMO 資料庫中的資料表，其中會有 F1 , F2 , F3 的欄位
正常來說使用者連到該資料庫，抓取 T1 資料表就可以取得 F1 , F2 , F3 這三個欄位。而為了要讓另外一個帳號，去讀取 T1 這個名稱的時候，只要出現 F1 , F2 這兩個欄位，因此我們在系統建立一個登入帳號 test ( 下圖 1 )，然後讓該帳號對應到資料庫中的使用者 test ( 下圖 2 ) ，並且建立一個新的 test.T1 的 View ( 下圖 3 )
此時我們讓 test 這個帳號的 Default Schema 也設定成為 test 那個 schema
這樣當 test 這個使用者登入該資料庫，如果下 select * from T1，這個時候因為沒有指定 schema，則會按照 Default Schema 的設定，讀取到 test.T1 那個 view ，也完成我們所想要的。
原本按照前面的處理方式，也順利的讓兩個系統各取所需，也不會因為修改欄位造成另外一個系統出問題，算是完成一個簡單任務。但就這樣想說沒有問題的時候，忽然今天收到訊息說 test 帳號沒有辦法讀取到那個 Default schema 下面的那個 view，而變成讀取到 dbo.T1 的那個資料表了。
本來好好的應該不會臨時有問題，因此詢問一下是否有人做了相關調整，但大家都表示沒有，只是一步一步的測試看看。從下面的指令和資料來看，的確使用者是 test 的時候，讀取到的是 T1 的資料表，要特別指定 schema 的名稱，才會讀到 test.T1 那個 view 的資料，這似乎有點詭異，應該不大可能會失效才對。
為了驗證使用者和權限的設定正確，我查看一下使用者的設定，看起來預設結構描述還是 test，權限依然是 db_datareader，似乎真的如負責人員所說，都沒有做任何修改，就「忽然」失效了…..
就在查看原因的時候，忽然閃過一個念頭，總不會使用者的權限被放大了吧，於是拿個指令測試一下
SELECT USER_NAME() [USER],* INTO T2 FROM T1;
GO
沒有想到上面的指令居然成功了，雖然我們權限是設定 db_datareader，照理說不應該有建立和插入資料的權限才對，因此我決定查一下登入帳號的權限。果然跟我想像的一樣，該登入帳號被賦予了 sysadmin 的角色，因此造成使用者對資料庫有最大的權限，因此這個時候雖然有預設 schema 是 test ，但還是會直接讀取 dbo 下面的物件。
因此當我將 test 帳號移除 sysadmin 的角色之後，果然就可以按照資料庫內使用者所對應的 schema 去撈資料了。
