SQL Server 基本許可權管理[備忘]

 

在SQL Server 中，使用系統的預存程序管理登入名稱，主要包括：

sp_addlogin       sp_droplogin       sp_helplogins

sp_grantlogin     sp_revokelogin    sp_denylogin

需要注意的是：這些系統的預存程序必須在master 資料庫中使用。

 

以下代碼實現簡單的三層許可權：

use S100801A
go

--======================= 資料庫登入 =======================
--添加登入使用者（sp_addlogin '使用者名稱','密碼','預設資料庫名'）
exec sp_addlogin N'xg',N'xg123'
-- 或者
exec sp_addlogin N'xg',N'xg123',N'master'

--移除登入使用者
exec sp_droplogin N'xg'

--======================= 資料庫訪問 =======================

--添加資料庫訪問使用者（sp_grantdbaccess '使用者名稱','資料庫名'）
use S100801A
go
exec sp_grantDBaccess N'xg',N'db_xg'

--移除資料庫訪問使用者 
exec sp_revokeDBaccess N'db_xg'

--======================= 資料庫許可權 =======================

--授予對資料庫的所有許可權（sp_addRoleMember '角色','使用者'）
exec sp_addRoleMember N'db_owner',N'db_xg'
--只能訪問自己的資料庫,及guest使用者的公用表      

--移除對資料庫的所有許可權（sp_dropRoleMember '角色','使用者'）
exec sp_dropRoleMember N'db_owner',N'db_xg'

/*==========================================================================
重要事項：
後續版本的 Microsoft SQL Server 中，將刪除使用‘系統預存程序’實現存取權限的相關功能。
為了避免在新的開發工作中使用該功能，應著手修改實現該功能的方式。
==========================================================================*/

 

改用實現方式如下：--以下樣本首先建立名為‘xg’且具有密碼的伺服器登入名稱，
--然後在資料庫‘S100801A’中建立使用者‘abo_xg’對應登入名稱‘xg’。

--添加登入使用者
create login xg with password = 'xg123';

--移除登入使用者
drop login xg;

--添加資料庫訪問使用者
use S100801A;
create user dbo_xg for login xg;
go 

--移除資料庫訪問使用者
use S100801A;
drop user dbo_xg;
go
 
--分布許可權
grant insert,select,update,delete on students to public --dbo_xg    
revoke insert,select,update,delete on students from public --dbo_xg

-- grant all on Students to aa_0801
-- revoke all on students  from aa_0801

使用SQL 命令分配許可權時，可以通過使用 grant、deny、revoke 等命令實現。