none
vollständiger Rechteentzug im SQL Server per Skript RRS feed

  • Frage

  • Hallo an alle,

    ich habe einen SQL Server vor mir der leider nicht neu aufgesetzt werden kann. Ich muss jedoch etwas einfallen lassen wie ich diversen Benutzerkonten die Rechte entziehen kann. 

    Deswegen wollte ich mal an dieser Stelle nachfragen ob jemand ein Skript besitzt, um alle Rechte eines Benutzeraccounts (AD) bzw. Benutzers (SQL Serverbenutzer) die Rechte auf public zu schrumpfen.

    Jedoch stellt sich mir auch auch die Frage ob es durch dieses Vorgehen noch zu Problemen kommen kann. Abgesehen von den nicht mehr vorhandenen Rechten. 

    Muss man hier auf etwas achten? Habe ich etwas nicht bedacht?

    Bin dankbar für jeden Tipp oder Hinweis.  


    Montag, 28. Oktober 2019 16:33

Alle Antworten

  • Hi,

    warum willst Du die kompletten Rechte entziehen und nicht den Account sperren bzw. die Anmeldung an den Server verweigern? (Oder gleich den ganzen Account löschen?)

    Natürlich könnte es zu Problemem kommen. Kann aber auch sein, dass es keine Probleme gibt. Das kommt aber alles darauf an, was Du unter Probleme verstehst!?

     


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport


    Montag, 28. Oktober 2019 16:56
    Moderator
  • Hallo Stefan,

    danke für deine Antwort. Ich möchte den Account nicht löschen oder deaktivieren da diese noch genutzt werden. Die Rechte sind aber alle zu hoch (SA für alle). Mir liegen aktuelle von diversen Anbietern die benötigten Rechte vor. Diese möchte ich nun umsetzen. Dazu möchte ich vorher sicherstellen das alle Accounts auf den "gleichen" Stand sind. Deswegen erst einmal kompletter Rechteentzug. 

    Dienstag, 29. Oktober 2019 07:32
  • Hi,

    es gibt viele Stellen, an denen unterschiedlichste Rechte gesetzt werden können.

    Wenn es dir erstmal nur darum geht, den Usern die SA Zuordnung zu nehmen ("Lob" an den DBA, der diesen Kram verbockt hat^^), geht das bspw. so:

    ALTER SERVER ROLE [sysadmin] DROP MEMBER [Anmeldungsname]

    Hinzufügen/wegnehmen anderer Serverrollen ebenfalls auf diese Art.

    Wenn Du auch auf Datenbankebene Rollenzuordnungen entfernen willst:

    ALTER ROLE [Datenbankrolle] DROP MEMBER [Datenbankbenutzername]

    Spezififsche Berechtigungen auf einzelne Objekte kann bspw. so:

    REVOKE <Berechtigung> ON [Schema].[Objektname] TO [Datenbankbenutzername]

    usw.

    Wenn das aber sehr viele unterschiedlichste Zuordnungen und die nicht dokumentiert sind, wäre es wohl sinnvoller, die Logins zu entfernen und neu anzulegen. Wenn man das Kennwort der User kennt, prima. Ansonsten bekommen Sie halt neue. Wichtig dabei ist aber u.a. u wissen, dass Benutzer, auch wenn sie den identischen Benutzernamen erhalten, nicht mit dem alten Benutzer übereinstimmen, da diese intern mit einer eindeutigen ID geführt werden und diese unterscheidet sich dann.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Dienstag, 29. Oktober 2019 07:44
    Moderator
  • Hallo Toot!

    Das hier ist ein etwas längeres Skript, aber es spuckt eine Menge Statements aus, die den User wieder aus dem SQL Server heraus operieren. Dann siehst Du mal, wo alles Rechte sind und kannst diese dann auch gezielt entfernen. Nimm das Skript, so wie es ist, ohne Garantie auf Vollständigkeit!

    Prüfe vorher, ob wirklich keine Updates stattfinden, sondern nur Statements generiert werden. Zumindest am Ende bei den Subscriptions musst Du tätig werden, wenn Du das UPDATE wirklich machen willst.

    Am Anfang stehen ein paar Platzhalter, die Du ersetzen musst.

    /*
    	<Zu ändernder Account>		-- Dieser Account soll geändert werden
    	<Neuer Account>				-- Das ist der neue Account, der die Rechte übernimmt
    	<Operator>					-- Falls es auch einen Operator für den Account gibt
    */
    
    Set Nocount on
    
    Declare @Test char(1) = 'J';
    
    
    declare @cmd1 varchar(2000)
    create table #db_permission(Datenbank sysname, principal_name sysname, principal_type_desc sysname, [login] sysname NULL, class_desc sysname NULL, [object_name] sysname NULL, permission_name sysname NULL, permission_state_desc sysname NULL, role_name sysname NULL)
    -- drop table #db_permission
    
    set @cmd1 ='use [?] ; WITH    perms_cte(principal_name,principal_id, principal_type_desc,class_desc, [object_name], permission_name, permission_state_desc, login ) as
    (
            select USER_NAME(p.grantee_principal_id) AS principal_name,
                    dp.principal_id,
                    dp.type_desc AS principal_type_desc,
                    p.class_desc,
                    OBJECT_SCHEMA_NAME(p.major_id) +''.''+OBJECT_NAME(p.major_id) AS [object_name],
                    p.permission_name,
                    p.state_desc AS permission_state_desc,
                                    sp.name as login
            from    sys.database_permissions p
            left        JOIN sys.database_principals dp on     p.grantee_principal_id = dp.principal_id
                    left        Join sys.server_principals sp on dp.sid = sp.sid
    )
    Select *
    from (
    -- users
    SELECT db_name() as Datenbank, p.principal_name,  p.principal_type_desc, login, p.class_desc, p.[object_name], p.permission_name,        p.permission_state_desc, cast('''' as sysname) as role_name
    FROM    perms_cte p
    UNION
    -- role members
    SELECT db_name(), rm.member_principal_name, rm.principal_type_desc, rm.login, p.class_desc, p.[object_name], p.permission_name,        coalesce(p.permission_state_desc, ''''), rm.role_name
    FROM    perms_cte p
    right outer JOIN (
        select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,           user_name(role_principal_id) as role_name, sp.name as login
        from    sys.database_role_members rm
        INNER   JOIN sys.database_principals dp ON     rm.member_principal_id = dp.principal_id
            left        Join sys.server_principals sp on dp.sid = sp.sid
    ) rm
    ON     rm.role_principal_id = p.principal_id
    ) x
    where x.principal_name <> ''public''
    '
    
    insert into #db_permission
    exec sp_MSforeachdb @command1=@cmd1 
    
    -- Zeige den User mit seinen Berechtigungen an
    If @Test = 'J'
    	select *
    	from #db_permission
    	where principal_name like  '<Zu ändernder Account>%'
    	or login like  '<Zu ändernder Account>%'
    	order by Datenbank, principal_type_desc, principal_name, class_desc, [object_name], permission_name, role_name;
    
    
    -- Entferne die Berechtigungen 
    select 'USE ' + Datenbank + ';'+ CHAR(13)+ CHAR(10) + 'GO ' + CHAR(13)+ CHAR(10) + 'REVOKE ' + permission_name + ' on ' + Datenbank + '.' + [OBJECT_NAME] + ' from [' + principal_name +'];' as Entferne_Rechte
    from #db_permission
    where (principal_name like  '<Zu ändernder Account>%'
    or login like  '<Zu ändernder Account>%')
    and [OBJECT_NAME] is not null
    order by Datenbank, principal_type_desc, principal_name, class_desc, [object_name], permission_name, role_name;
    
    -- Entferne den User
    select distinct 'USE ' + Datenbank + ';'+ CHAR(13)+ CHAR(10) + 'GO ' + CHAR(13)+ CHAR(10) + 'DROP USER  ['  + principal_name +'];' as Loeschung_User
    from #db_permission
    where principal_name like  '<Zu ändernder Account>%'
    or login like  '<Zu ändernder Account>%'
    and principal_name <> 'dbo'
    ;
    
    
    -- Finde dbos
    select Distinct 'ALTER AUTHORIZATION ON DATABASE::' + Datenbank + ' TO [<Neuer Account>];' as Datenbank_Eigentuemer
    from #db_permission
    where datenbank not in ('master', 'model', 'msdb', 'tempdb')
    and principal_name = 'dbo'
    and login like  '<Zu ändernder Account>%'
    and role_name = 'db_owner';	
    
    -- Aufräumen
    drop table #db_permission
    
    -- Verbindungsserver?
    select 'EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname = N''' + srvname + ''', @locallogin = N''<Zu ändernder Account>'';' as Verbindungsserver
    from master..sysservers
    where srvid > 0;
    
    -- Packages
    select 'UPDATE msdb.dbo.sysssispackages SET [ownersid] = SUSER_SID(''<Neuer Account>'') WHERE [name] =''' + name + ''';' as Pakete
    from msdb.dbo.sysssispackages 
    where [ownersid] = SUSER_SID('<Zu ändernder Account>') ;
    
    -- Jobs?
    Select 'EXEC msdb.dbo.sp_update_job @job_id=N''' + cast(job_id as varchar(100)) + ''', @owner_login_name=N''<Neuer Account>''' as Job_Owner
    from msdb..sysjobs j
    inner join master..syslogins l on j.owner_sid = l.sid
    where l.loginname = '<Zu ändernder Account>';
    
    
    -- Server-Rollen
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''sysadmin'';' as Serverrollen from master..syslogins
    where sysadmin = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''securityadmin'';' as Serverrollen from master..syslogins
    where securityadmin = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''serveradmin'';' as Serverrollen from master..syslogins
    where serveradmin = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''setupadmin'';' as Serverrollen from master..syslogins
    where setupadmin = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''processadmin'';' as Serverrollen from master..syslogins
    where processadmin = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''diskadmin'';' as Serverrollen from master..syslogins
    where diskadmin = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''dbcreator'';' as Serverrollen from master..syslogins
    where dbcreator = 1
    and name = N'<Zu ändernder Account>'
    union
    select  'EXEC master..sp_dropsrvrolemember @loginame = N''<Zu ändernder Account>'', @rolename = N''bulkadmin'';' as Serverrollen from master..syslogins
    where bulkadmin = 1 
    and name = N'<Zu ändernder Account>';
    
    
    -- Login droppen?
    Select 'DROP Login [<Zu ändernder Account>];' as Login_Droppen;
    
    /* Falls es hier Probleme gibt, wurden evtl. Rechte an ein anderes Login über dieses Login erteilt:
    
    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/09/drop-failed-for-login-since-it-has-granted-one-or-more-permission-s.aspx
    
    Select * from sys.server_permissions 
    where grantor_principal_id = 
    (Select principal_id from sys.server_principals where name = N'<Zu ändernder Account>') 
    
    Select *
    from sys.server_principals 
    where principal_id in (260, 262);
    */
    
    -- Operator droppen
    Select 'USE [msdb]
    GO
    
    IF  EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N''<Operator>'')
    EXEC msdb.dbo.sp_delete_operator @name=N''<Operator>'';
    GO' as Operator_droppen;
    
    
    go
     
     ----------------------------------------------------------------------------------------------------------------------------------
    -- Availability Groups
    -- Endpoints
    
    -- How to check the current settings
    USE master; 
     
    SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name 
    FROM sys.database_mirroring_endpoints; 
     
    -- Check and record any permissions. You will need this, because changing to sa REVOKES all permissions granted before!
    SELECT EPS.name, SPS.STATE, CONVERT(nvarchar(38), SUSER_NAME(SPS.grantor_principal_id))AS [GRANTED BY], SPS.TYPE AS PERMISSION, CONVERT(nvarchar(46),SUSER_NAME(SPS.grantee_principal_id))AS [GRANTED TO] 
    FROM	sys.server_permissions SPS 
    		Inner Join sys.endpoints EPS 
    			on SPS.major_id = EPS.endpoint_id 
    WHERE name = 'Hadr_endpoint'
    ORDER BY Permission,[GRANTED BY], [GRANTED TO]; 
    
    -- Now run your batch. You cannot grant the permission to yourself, change the connection to sa beforehand!
    -- Be carefull to add in all accounts you have recorded before, because changing to sa REVOKES all permissions granted before!
    /*
    	BEGIN TRAN USE master; 
    	ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa; 
    	GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [yourdomain\username]; 
    	COMMIT
    */
    
    
    -- AGs
    -- Check
    SELECT ar.replica_server_name
           ,ag.name AS ag_name
           ,ar.owner_sid
           ,sp.name
    FROM sys.availability_replicas ar
    LEFT JOIN sys.server_principals sp
           ON sp.sid = ar.owner_sid 
    INNER JOIN sys.availability_groups ag
           ON ag.group_id = ar.group_id
    WHERE ar.replica_server_name = SERVERPROPERTY('ServerName') ;
    
    -- Now run your batch.
    /*
    	ALTER AUTHORIZATION ON AVAILABILITY GROUP::<AG-Name> to [sa] ;
    */
    
    
     /* 
    	ACHTUNG
    	=======
    	Die Datenbank-Owner (dbo) der Datenbanken auf dem Primary lassen sich ändern.
    	Die Datenbank-Owner (dbo) der Datenbanken auf dem Secondary bleiben auf dem alten Stand.
    	Hierzu entweder Failover abwarten oder Datenbank aus der AG entfernen und neu aufnehmen. Der dafür verwendete User wird dbo.
    */
    
    
    
    
     ----------------------------------------------------------------------------------------------------------------------------------
     
     /*
    Subscriptions umbiegen
    http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx
    
    */
    Use ReportServerPBI
    go
    -- Dieses Stück Code für jedes Konto aus dem unten aufgeführten SQL vorbereiten
    DECLARE @OldUserID uniqueidentifier;   
    DECLARE @NewUserID uniqueidentifier;   
    SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = '<Zu ändernder Account>';   
    SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = '<Neuer Account>';   
    
    -- ACHTUNG: Die folgende Zeile dann auskommentieren
    -- UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID;
    
    
    
    
    
    -- Welche User haben überhaupt Subscriptions?
    select u.Username, s.OwnerID, count(*) as Anzahl_Subscriptions
    from dbo.Subscriptions s 
    inner join dbo.Catalog c
    	on s.Report_OID = c.ItemID
    inner join dbo.users u
    	on s.ownerid = u.userid	
    group by u.Username, s.OwnerID
    order by u.Username;


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Mittwoch, 30. Oktober 2019 07:20
    Beantworter