SQL Server查看login所授予的具體許可權

来源:https://www.cnblogs.com/kerrycode/archive/2019/09/25/11588227.html
-Advertisement-
Play Games

在SQL Server資料庫中如何查看一個登錄名(login)的具體許可權呢,如果使用SSMS的UI界面查看登錄名的具體許可權的話,用戶資料庫非常多的話,要梳理完它所有的許可權,操作又耗時又麻煩,個人十分崇尚簡潔、高效的方法,反感那些需要大量手工操作的UI界面操作方式,哪怕就是腳本,如果不能一次搞定,手工... ...


在SQL Server資料庫中如何查看一個登錄名(login)的具體許可權呢,如果使用SSMS的UI界面查看登錄名的具體許可權的話,用戶資料庫非常多的話,要梳理完它所有的許可權,操作又耗時又麻煩,個人十分崇尚簡潔、高效的方法,反感那些需要大量手工操作的UI界面操作方式,哪怕就是腳本,如果不能一次搞定,手工多操作幾次(例如,切換資料庫),都是不可接受的。最近遇到這個需求,就完善了一下之前的腳本get_login_rights_script.sql,輸入登錄名參數,將這個登錄名所擁有的伺服器角色、資料庫角色、以及所授予具體對象的相關許可權使用腳本查詢出來,腳本分享如下:

 

--==================================================================================================================
--        ScriptName            :            get_login_rights_script.sql
--        Author                :            瀟湘隱者    
--        CreateDate            :            2015-12-18
--        Description           :            查看某個登錄名被授予的資料庫對象的許可權的腳本(授權腳本和回收許可權腳本)
--        Note                  :             
/******************************************************************************************************************
        Parameters              :                                    參數說明
********************************************************************************************************************
            @login_name         :            你要查看許可權的登錄名(需要輸入替換的參數)
********************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
********************************************************************************************************************
    2018-08-03        瀟湘隱者         V01.00.00        新建該腳本。
    2019-04-04        瀟湘隱者         V01.01.00        Fix掉一個bug,某個表只允許更新某個欄位,但是這裡顯示更新整個表。
    2019-09-25        瀟湘隱者         V01.02.00        解決只能查看某個用戶資料庫,不能查看所有資料庫的許可權問題。
    2019-09-25        瀟湘隱者         V01.03.00        解決資料庫名包含中劃線[-], 出現下麵錯誤問題
-------------------------------------------------------------------------------------------------------------------
Msg 911, Level 16, State 1, Line 1
Database 'xxxx' does not exist. Make sure that the name is entered correctly.
-------------------------------------------------------------------------------------------------------------------
   2019-09-26         瀟湘隱者         V01.04.00        解決系統表和系統視圖大小寫問題(排序規則區分大小時,會報錯)
   2019-09-26         瀟湘隱者         V01.04.00        加入資料庫角色詳細信息
*******************************************************************************************************************/
 
DECLARE @login_name     NVARCHAR(32)= 'test1';
DECLARE @database_name  NVARCHAR(64);
DECLARE @cmdText        NVARCHAR(MAX);
 
 
IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
    DROP TABLE dbo.#databases;
 
CREATE TABLE #databases
(
    database_id     INT,
    database_name   sysname
);
 
IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL 
    DROP TABLE dbo.#user_db_roles;
 
 
CREATE TABLE dbo.#user_db_roles
(
     [DB_NAME]                NVARCHAR(64)
    ,[USER_NAME]              NVARCHAR(64)
    ,[ROLE_NAME]              NVARCHAR(64)
    ,[PRINCIPAL_TYPE_DESC]    NVARCHAR(64)
    ,[CLASS_DESC]             NVARCHAR(64)
    ,[PERMISSION_NAME]        NVARCHAR(64)
    ,[OBJECT_NAME]            NVARCHAR(128)
    ,[PERMISSION_STATE_DESC]  NVARCHAR(128)
);
 
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
    DROP TABLE dbo.#user_object_rights;
 
CREATE TABLE dbo.#user_object_rights
(    
    [DATABASE_NAME]        NVARCHAR(128),
    [SCHEMA_NAME]          NVARCHAR(64),
    [OBJECT_NAME]          NVARCHAR(128),
    [USER_NAME]            NVARCHAR(32),
    [PERMISSIONS_TYPE]     CHAR(12),
    [PERMISSION_NAME]      NVARCHAR(128),
    [PERMISSION_STATE]     NVARCHAR(64),
    [CLASS_DESC]           NVARCHAR(64),
    [COLUMN_NAME]          NVARCHAR(32),
    [STATE_DESC]           NVARCHAR(64),
    [GRANT_STMT]           NVARCHAR(MAX),
    [REVOKE_STMT]          NVARCHAR(MAX)
)
 
INSERT  INTO #databases
SELECT  database_id ,
        name
FROM    sys.databases
WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE 
 
 
--登錄名授予的伺服器角色
SELECT  UserName          = u.name ,
        ServerRole        = g.name ,
        Type              = u.type,
        Type_Desc         = u.Type_Desc,
        Create_Date       = u.create_date,
        Modify_Date       = u.modify_date, 
        DenyLogin         = l.denylogin
FROM    sys.server_role_members m
        INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id
        INNER JOIN sys.server_principals u ON u.principal_id = m.member_principal_id
        INNER JOIN sys.syslogins l ON u.name = l.name
WHERE l.name=@login_name
ORDER BY u.name,g.name;
 
 
WHILE 1= 1
BEGIN
 
 
    SELECT TOP 1 @database_name= database_name   
    FROM #databases
    ORDER BY database_id;
 
    IF @@ROWCOUNT =0 
        BREAK;
 
 
    SET @cmdText =  N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)
 
    --登錄名授予的資料庫角色
    /********************************************************************************
    SELECT @cmdText += N'INSERT INTO #user_db_roles
                        SELECT  DB_NAME()     AS [DB_NAME]
                               ,M.NAME        AS [USER_NAME]
	   

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 話不多說上代碼 linux環境:centos 7.0 64位 nginx:nginx-1.8.0.tar.gz php: php-7.1.1.tar.gz mysql: mysql-5.6.21.tar.gz libxml2:libxml2-2.9.1.tar.gz openssl:openssl- ...
  • 1.ls 2.pwd 3.cd 4.mkdir 常用選項: -p 創建級聯目錄 創建目錄 5.touch 修改時間戳 平時都是用來創建普通文件 6.cp 複製文件和目錄 常用選項: -p: 保持屬性不變 -r: 遞歸複製目錄 -a: 複製時,儘可能保持文件的結構和屬性. 7.mv 移動 (改名) 文 ...
  • 名詞解釋: CV:Constant Voltage恆壓 SMMB charger:Switch ModeBattery Charger and Boost peripheral開關模式電池充電器和升壓外圍設備 OCV:Open circuitvoltage EOC:End OfCharge充電結束 ...
  • 本章內容: CentOS 8 的安裝(CentOS-8-1905) 一、安裝光碟,選擇Install CentOS Linux 8.0.1905 二、選擇系統語言,我這裡選的是英文,也可以選擇中文,往下拉就行 三、設置鍵盤、分區、系統語言、安裝方式(我這裡選擇最小化安裝,也可以選擇GUI安裝-有界面 ...
  • 本文首發於:微信公眾號「運維之美」,公眾號 ID:Hi Linux。 「運維之美」是一個有情懷、有態度,專註於 Linux 運維相關技術文章分享的公眾號。公眾號致力於為廣大運維工作者分享各類技術文章和發佈最前沿的科技信息。公眾號的核心理念是:分享,我們認為只有分享才能使我們的團體更強大。如果你想第一 ...
  • USB Battery Charging V1.2 Specification 定義了USB充電器的類型或者叫做充電源。 1. 支持的充電器類型 1.1 Standard Downstream Port(SDP) 這種USB埠存在於主機PC中,這個是與USB的規格書一致的。 當一個USB外設接到S ...
  • 毋庸置疑,操作系統(Operating System,OS)是一個非常大的概念,涉及到的內容非常非常多,在探討它的時候,往往會將操作系統置於一個比較底層的角度去對待,這也使得多數人對OS是“聞之喪膽”,對OS相關的資料或概念也是望而卻步,這也是我曾經親身經歷的過程。 君不見那些力量之書自地獄來。 這 ...
  • 一、一般查找:find find PATH -name FILENAME 我們也可是使用 ‘*’ 通配符來模糊匹配要查找的文件名 二、資料庫查找:locate locate FILENAME 1、這種方法相對於find命令反饋更為迅速 2、在使用這個命令之前,一般需要執行updatedb ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...