April 21, 2010 at 11:57 am
Can anyone help me with providing with script to delete some specific logins and users assosiated with that login from the SQL Server 2005
April 21, 2010 at 12:27 pm
SET NOCOUNT ON
DECLARE @DB SYSNAME
,@OldUser SYSNAME
,@SQL VARCHAR(MAX)
SET @OldUser = '' --<----------------THIS VALUE NEEDS TO BY CHANGED----<<
DECLARE db CURSOR FAST_FORWARD FOR
SELECT name FROM master.sys.databases
WHERE State_desc = 'ONLINE'
AND is_read_only = 0
OPEN db
WHILE 0=0
BEGIN
FETCH NEXT FROM db
INTO @DB
IF @@FETCH_STATUS <> 0
BREAK
SET @sql =' USE [' + @DB + ']
DECLARE @DB_User SYSNAME
,@OBJECT_NAME SYSNAME
,@SCHEMA_NAME SYSNAME
SELECT @DB_User = dp.name
FROM sys.database_principals dp
JOIN sys.Server_Principals sp
ON dp.sid = sp.sid
WHERE sp.name = ''' + @OldUser + '''
IF @DB_User IS NOT NULL
BEGIN
PRINT ''USE [' + @DB + ']''
DECLARE OBJECTS CURSOR FAST_FORWARD FOR
SELECT so.name AS OBJECT_NAME, ss.name AS SCHEMA_NAME
FROM sys.objects so
JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
JOIN sys.database_principals dp
ON ss.principal_id = dp.principal_id
WHERE dp.Name = @DB_User
OPEN OBJECTS
WHILE 0=0
BEGIN
FETCH NEXT
FROM OBJECTS
INTO @OBJECT_NAME
,@SCHEMA_NAME
IF @@FETCH_STATUS <> 0
BREAK
PRINT ''ALTER SCHEMA dbo TRANSFER ['' + @SCHEMA_NAME + ''].['' + @OBJECT_NAME + '']''
END
CLOSE OBJECTS
DEALLOCATE OBJECTS
DECLARE SCHEMAS CURSOR FAST_FORWARD FOR
SELECT ss.name AS SCHEMA_NAME
FROM sys.schemas ss
JOIN sys.database_principals dp
ON ss.principal_id = dp.principal_id
WHERE dp.Name = @DB_User
OPEN SCHEMAS
WHILE 0=0
BEGIN
FETCH NEXT
FROM SCHEMAS
INTO @SCHEMA_NAME
IF @@FETCH_STATUS <> 0
BREAK
PRINT ''DROP SCHEMA ['' + @SCHEMA_NAME + '']''
END
CLOSE SCHEMAS
DEALLOCATE SCHEMAS
PRINT ''DROP USER ['' + @DB_User + '']''
END
'
EXEC(@SQL)
END
CLOSE DB
DEALLOCATE DB
IF EXISTS( SELECT * FROM master.sys.server_principals WHERE name = @OldUser )
PRINT 'USE MASTER
DROP LOGIN [' + @OldUser + ']'
April 21, 2010 at 11:38 pm
Thanks for replying. My requirements are following :
a) On few of my Servers Mirroring is configured. I need to remove the login which has created the Mirroring End points . So what is the best way to transfer the ownership for this login to the other without disturbing the mirroring environment.
April 22, 2010 at 2:06 am
The example below transfers ownership of the Database Mirroring endpoint to ‘sa’:
USE master
GO
ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO [sa]
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply