SQL Server--ChangeAllSysadminPermission/List All Of The Sysadmin Permission

Posted by: bart30508 | in SQL Server | 11 months ago |

SQL Server--ChangeAllSysadminPermission/List All Of The Sysadmin Permission

List all of the sysadmin permission

SELECT r.name AS ROLE
    ,m.name AS Principal
FROM master.sys.server_role_members rm
INNER JOIN master.sys.server_principals r ON r.principal_id = rm.role_principal_id
    AND r.type = 'R'
INNER JOIN master.sys.server_principals m ON m.principal_id = rm.member_principal_id
WHERE r.name = 'sysadmin'

Change all aysadmin permission most likely the same permission

DECLARE @ServerRule NVARCHAR(100)
    ,@Login NVARCHAR(100)
    ,@Query NVARCHAR(4000)

DECLARE MYCURSOR CURSOR
FOR
SELECT r.name AS ROLE
    ,m.name AS Principal
FROM master.sys.server_role_members rm
INNER JOIN master.sys.server_principals r ON r.principal_id = rm.role_principal_id
    AND r.type = 'R'
INNER JOIN master.sys.server_principals m ON m.principal_id = rm.member_principal_id
WHERE r.name = 'sysadmin'
    AND m.name LIKE 'AP%'
    AND m.name IN (
        'AP\WuZ10'
        ,'AP\ChenJ47'
        ,'AP\LuCL'
        ,'AP\FuH5'
        ,'AP\TaiRD'
        )

OPEN MYCURSOR

FETCH NEXT
FROM MYCURSOR
INTO @ServerRule
    ,@Login

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = '
    USE [master]
    GO
    ALTER SERVER ROLE [sysadmin] DROP MEMBER [' + @Login + ']
    GO
    ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @Login + ']
    GO
    ALTER SERVER ROLE [dbcreator] ADD MEMBER [' + @Login + ']
    GO
    ALTER SERVER ROLE [diskadmin] ADD MEMBER [' + @Login + ']
    GO
    ALTER SERVER ROLE [processadmin] ADD MEMBER [' + @Login + ']
    GO
    ALTER SERVER ROLE [securityadmin] ADD MEMBER [' + @Login + ']
    GO
    ALTER SERVER ROLE [setupadmin] ADD MEMBER [' + @Login + ']
    GO

    USE [msdb]
    GO
    CREATE USER [' + @Login + '] FOR LOGIN [' + @Login + ']
    GO
    USE [msdb]
    GO
    ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [' + @Login + ']
    GO
    USE [msdb]
    GO
    ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [' + @Login + ']
    GO
    USE [msdb]
    GO
    ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [' + @Login + ']
    GO
    USE [msdb]
    GO
    ALTER ROLE [SQLAgentUserRole] ADD MEMBER [' + @Login + ']
    GO
    '

    PRINT (@Query)

    -- EXEC(@Query)
    FETCH NEXT
    FROM MYCURSOR
    INTO @ServerRule
        ,@Login
END

CLOSE MYCURSOR

DEALLOCATE MYCURSOR

參考來源:

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database https://www.netwrix.com/how_to_check_user_roles_in_sql_server.html

tags: SQL Server
Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (146)

Feeds

RSS / Atom

SQL Server--ChangeAllSysadminPermission/List All Of The Sysadmin Permission

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.