SQL server 語句建立使用者、對使用者授權、刪除使用者執行個體

標籤：

Grant select on tb to db_user--給db_user使用者授權 tb表 查詢許可權

一、命令操作

USE mydb

GO

--1. 建立測試使用者

--1.1 添加登入使用者和密碼

EXEC sp_addlogin N‘tony‘,‘123‘

--1.2 使其成為當前資料庫的合法使用者

EXEC sp_grantdbaccess N‘tony‘

 

--2.設定作業授權

--2.1 授予對自己資料庫的所有許可權

EXEC sp_addrolemember N‘db_owner‘, N‘tony‘

 

--2.2 以下是設定具體操作許可權

--授予tony對所有使用者表的操作許可權

GRANT SELECT,INSERT,UPDATE,DELETE TO tony

--授予tony SELECT,UPDATE到具體的表

GRANT SELECT,UPDATE ON tb TO tony

--授予tony SELECT,UPDATE到具體的表和列

GRANT SELECT,UPDATE ON tb(id,col) TO tony

--禁止tony對所有使用者表的操作許可權

DENY SELECT,INSERT,UPDATE,DELETE TO tony

--禁止tony SELECT,UPDATE到具體的表

DENY SELECT,UPDATE ON tb TO tony

--禁止tony SELECT,UPDATE到具體的表和列

DENY SELECT,UPDATE ON tb(id,col) TO tony

--刪除tony 對所有使用者表的授權資訊

REVOKE SELECT,INSERT,UPDATE,DELETE TO tony

 

--授予tony對具有建立表、視圖、預存程序等的操作許可權

GRANT CREATE TABLE,CREATE VIEW,CREATE PROC TO tony

--禁止tony對具有建立表、視圖、預存程序等的操作許可權

DENY CREATE TABLE,CREATE VIEW,CREATE PROC TO tony

--刪除tony對具有建立表、視圖、預存程序等的授權資訊

REVOKE CREATE TABLE,CREATE VIEW,CREATE PROC TO tony

 

GO

--註：更多相關授權資訊參考後面的附表中“資料庫許可權”列。

 

--3. 刪除測試使用者

EXEC sp_revokedbaccess N‘tony‘ --移除使用者對資料庫的存取權限

EXEC sp_droplogin N‘tony‘ --刪除登入使用者

GO

 

 

二、手動分配

 

一、需求

在管理資料庫過程中，我們經常需要控制某個使用者訪問資料庫的許可權，比如只需要給這個使用者訪問某個表的許可權，甚至是CRUD的許可權，更小粒度的還可以去到某幾個欄位的存取權限。寫這篇文章就是說明下這個操作過程。

其實這隻是SQL Server許可權管理很簡單的一小塊，有些地方並沒有深入理解和講述，只是希望對一些剛入門的童鞋有協助，其它大俠就當是：我當堂嚇一跳，然後得啖笑。（賭聖）

 

二、操作步驟

1.      首先進入資料庫層級的【安全性】-【登入名稱】-【建立登入名稱】

（圖1：建立登入名稱）

2.      在【常規】選項卡中，如所示，建立登陸名，並設定預設的資料庫

（圖2：設定選項）

3.      在【使用者映射】選項卡中，如所示，勾選需要設定的資料庫，並設定【架構】，點擊【確認】按鈕，完成建立使用者的操作

（圖3：選擇對應資料庫）

4.      現在我們就可以對TestLog資料庫中的User表進行許可權的設定了，【表】-【 屬性】

（圖4：選擇對應表）

5.      在【許可權】選項卡中，如所示，依此點擊【添加】-【瀏覽】-【選擇對象】

 

（圖5：設定訪問表的使用者）

6.      在上面點擊【確認】後，我們就可以下面的列表中找到對應的許可權，如果你還想細化到列的許可權的話，右下角還有一個【列許可權】的按鈕可以進行設定，點擊【確認】按鈕就完成了這些許可權的設定了

（圖6：許可權列表）

7.      現在就使用TestUser使用者登陸資料庫了，登陸後如所示，現在只能看到一個表了

（圖7：效果）

 

三、注意事項

1.      在上面的第3步驟中需要注意：如果這裡沒有選擇對應的資料庫的話，之後去TestLog資料庫中是找不到TestUser。

（圖8：找不到TestUser使用者）

2.      在上面的第3步驟，設定完TestLog資料後，需要點擊【確認】按鈕，完成建立使用者操作，如果這個時候去設定【安全性實體】，是無法在【添加】-【特定對象】-【物件類型】-【登陸名】-【瀏覽】中找到剛剛建立的TestUser使用者的。

3.      其實在資料庫層級的【安全性】建立的使用者是屬於全域的，當設定了某個資料庫，比如TestLog之後，這個使用者就會出現在這個資料庫的【安全性】列表中。如果刪除TestLog這個使用者，會出現下面的提示。刪除了後，這個使用者就無法登陸了。需要去對應的資料庫中刪除使用者，如果沒有刪除又建立，是會報錯的。

（圖9：刪除TestUser使用者）

4.      在第6步的【明確權限】列表中，如果選擇了【Control】這個選項，那麼在【Select】中設定查詢【列許可權】就沒有意義了，查詢就不會受限制了。如果設定【列許可權】，在正常情況下會顯示的報錯資訊：

（圖10：效果）

5.      在TestLog資料庫的【安全性】-【TestUser】-【屬性】-【安全性實體】-【添加】-【物件類型】這裡有更多關於資料庫層級的一些物件類型可以設定。

（圖11：其它物件類型）

SQL server 語句建立使用者、對使用者授權、刪除使用者執行個體