程式碼學習不歸路

在此篇開始前，需先了解一些權限控管的基本字眼

可以先參考一下 Will 保哥之前寫的資料，非常詳細，在此就簡單介紹

Login: 主要是以 db server 為主體，需建立登入的帳號與密碼，通常有了 login 才能往下創建 DataBase User

DataBase User: 以 database 為主體，不需建立密碼，但需設定 security principle, 比方說能存取哪些 table

以下為例，創建了 test 帳號後，sql server 並不會主動幫你創建 database user

你有可能會需要在所有的 database 上面設定同一個 database user

 而database 的資訊是儲存在 master 的 database 裡，即便是 azureDB 也是一樣的

SELECT  name FROM master.dbo.sysdatabases 

 

CREATE LOGIN [test] WITH PASSWORD='test'

 

在azureDB上，請跳至你需要的 database

若不是 azureDB，可以使用 use xxxDB 的指令較為便捷 

CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]

 

找出資料庫裡所有為 dbo schema的資料表 

 SELECT TABLE_NAME
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
 and TABLE_SCHEMA = 'dbo'

 

基本指令為 

GRANT SELECT ON OBJECT::[dbo].[Table] TO test;  

但因為我們可以找出所有的 tables，就可以產生出批次的指令來做更改，否則在 GUI 畫面上可是要一個一個設定

如果今天有100 個以上的資料表，這個動作會節省你多少時間

我們修改一下取出所有 tables 的指令

 SELECT 'GRANT SELECT ON OBJECT::[dbo].['+TABLE_NAME+'] TO test;'  
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
 and TABLE_SCHEMA = 'dbo'

此時我們再把資料結果全部複製出來，一次執行就完成了

 

一般來說是sql server 沒有開啟 sql server登入的驗證方式，開啟後再重新啟動 sql server服務即可

可以參考一下這裡的作法 

 

在migration時，通常會是以一個 database 進行搬移，比如說是用 attach/detach 或是 fullbackup的方式

此時database 都還會保留著原本的 database user, 在新的 db server並不會有 login的資料

即便在 db server 創建了一組 test 的 login 帳號，在 GUI 畫面做 新增 database user 時即會出現無法新增已經存在的 user

通常而言我們會試圖先把舊有的 database user  先移除掉

drop user test

但如果這個 user 是 schema owner 的話，移除的動作就會失敗

可以先確認這個 user 到底 own 了哪些 schema，否則是無法移除的

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_OWNER= 'test'

如果發現的確有db_denydatawriter 的schema owner的話，可以暫時先把 schema 的控制權先轉換給 dbo 

ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] TO [dbo];

接著就可以順利的把 user drop 掉

drop user test

 

若是要要給予的是 db owner的權限的帳號的話，執行這段也可以解決，不過不建議給這麼大的權限就是了

exec sp_changedbowner test

 

 

參考資料

https://blog.miniasp.com/post/2011/08/20/SQL-Server-Security-and-Unable-to-delete-database-user-problem.aspx