מחיקת Login וכל ה-users שלו - ilDBA Portal

מחיקת Login וכל ה-users שלו

12/04/2011 | פורסם על ידי

ב SQL Server יש הפרדה בין הזדהות (login) למשתמש (user). ההזדהות מוגדרת בקטלוג הראשי של ה Instance – היינו database master והמשתמש מוגדר בכל database שיש לו גישה אליו (אם אין לו הרשאה שמאפשרת גישה כללית כגון sysadmin).

אם אנו רוצים למחוק את ה-login לחלוטין (כולל ה-user שמוגדר בכל אחד מה database-ים השונים) אנו צריכים לעבור אחד אחד על כל ה-database-ים למחוק את ה-user משם. כמובן שיש לשים לב למקרה שבו ה-user מוגדר גם owner של אובייקטים, כי במקרה כזה נרצה להעביר את ה ownership ל-user אחר.

בקיצור, על מנת לקצר את הפעולה המפרכת הזו, כתבתי סקריפט קצר שמדפיס פקודות על מנת למחוק את כל ה user-ים ולאחר מכן את ה login. אז לאחר שמריצים את הסקריפט, כל מה שנותר לעשות זה להריץ את ה output שלו

להלן הסקריפט:

set nocount on
declare @db sysname
,             @login sysname
,             @username sysname
,             @stmt nvarchar(4000)
--Use the login name that should be deleted
set @login = 'alons'

---------------------------------------------------------
-- get list of users from the databases
---------------------------------------------------------
declare dbs cursor local static for
          select name from sys.databases
open dbs
fetch next from dbs into @db
while @@fetch_status = 0
begin
   set @stmt = isnull(@stmt + char(10)+'union all'+char(10), '')
+'select '''+@db+''' db, u.name collate '
+cast(SERVERPROPERTY('Collation') as varchar)
+' username from '+@db
+'.sys.sysusers u join master.sys.syslogins l
on u.sid=l.sid where l.[name]='''+@login+''''
   fetch next from dbs into @db
end
close dbs
deallocate dbs

declare @users table ( db     sysname
                      ,username sysname
                     )
set @stmt = 'sp_executesql N''' + replace(@stmt, '''', '''''') + ''''
--print @stmt
insert into @users (db, username)
exec (@stmt )
---------------------------------------------------------
-- drop the users for that login
---------------------------------------------------------
declare drops cursor static for
         select * from @users
print 'BEGIN TRAN'

open drops
fetch next from drops into @db, @username
while @@fetch_status=0
begin
   print 'USE ['+@db+'];DROP USER ['+@username+']'
   fetch next from drops into @db, @username
end
close drops
deallocate drops

set @stmt = null
select @stmt = 'USE [master];DROP LOGIN ['+name+']'
from   master.sys.syslogins
where  [name] = @login

print @stmt
print 'COMMIT TRAN'

להלן ה-output

BEGIN TRAN

USE [mydb1];DROP USER [alons]
USE [mydb2];DROP USER [alons]
USE [master];DROP LOGIN [alons]

COMMIT TRAN
נתראה ברשומה הבאה.
אלון שפיגל
ניתן ליצור קשר עם אלון דרך עמוד האודות שלו: אודות
The following two tabs change content below.

תגובה אחת ל- “מחיקת Login וכל ה-users שלו”

גרי רשף | 13/04/2011 בשעה 00:11
commenter

יפה מאוד: בדקתי ועבד.

שתי הערות לגבי בעיות בהן נתקלתי:

1. יש לי דטבייסים עם שמות "משוגעים" ולכן יש להקיף אותם בסוגריים [מרובעים], ולשנות לשם כך את שורות 23-24 בקוד:
+' username from ['+@db
+'].sys.sysusers u join master.sys.syslogins l

2. המשתנה @stmt "נגמר לי" באמצע, ולכן השתמשתי ב- NVarchar Max (במקום 4000 כמוך).

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

קהילת

קהילת ה- BI וה- BIG DATA מתכנסת ב- 28-10 !

שלום רב, למי שלא ידע 🙂 קהילת ה- BI וה- BIG DATA בעולמות Microsoft מתכנסת מידי חודש על מנת להפגש, להכיר ולשמוע הרצאות במגוון נושאים טכנולוגיים מרתקים בתחום. במפגש הקרוב (מספר 63) שיתקיים ב- 28-10-2015, יום רביעי [...]
הזמנה

הזמנה ל-SQL Saturday #481 – Israel 2016

שלום רב, בקרוב יתקיים בישראל כנס טכנולוגי מרכזי קהילת ה-DB וה-BI בתחום ה-SQL Server – ה-SQL Saturday! הכנס אשר מאורגן בהתנדבות על ידי אנשי הקהילה יכלול במהלכו מספר מסלולי לימוד בתחומים טכנולוגיים שונים. [...]

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]

תיעוד בסיס הנתונים – למה זה טוב?

ישנם מספר נושאים חשובים שיש לתת עליהם את הדעת בפיתוח ותכנון בסיס נתונים. כמובן שצריך לשים לב לנירמול נכון של הטבלאות, וכמובן שצריך להגדיר סטנדרט לכתיבה נכונה של הקוד, וכמובן שאי אפשר בלי [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: