The below code can be used to drop Login from SQL Server and user name associated with this Login in different databases.
USE MASTER
GO
DECLARE @LoginName VARCHAR(100)
SET @LoginName='Domain\LoginName' --> Provide the LoginName Windows or SQL Server
DECLARE @DBName VARCHAR(1000)
IF EXISTS (SELECT *
FROM syslogins
WHERE loginname = @LoginName)
BEGIN
PRINT @LoginName + ' login exists'
DECLARE CUR CURSOR FOR
--Get the list of databases
SELECT name
FROM sys.databases
WHERE state = 0
AND is_read_only=0
--OPEN CURSOR
OPEN CUR
--Fetch First Row
FETCH NEXT FROM CUR INTO @DBName
--Loop
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(MAX);
DECLARE @BIGSQL NVARCHAR(MAX);
DECLARE @DropUSerSQL NVARCHAR(500)
DECLARE @DropLoginSQL NVARCHAR(500)
DECLARE @USER NVARCHAR(100)
IF OBJECT_ID('tempdb..##T') IS NOT NULL
DROP TABLE ##T
--> Get User Name for Login
SET @SQL='
(SELECT name into ##T FROM sys.database_principals where suser_sname(sid)='''''
+ @LoginName + '''''' + ')'
SET @BigSQL = 'USE [' + @dbName
+ ']; EXEC sp_executesql N''' + @sql + '''';
EXEC(@BIGSQL)
SET @USER=(SELECT Name
FROM ##T)
--> If User is owner of Database, Change the owner to sa
IF ( @USER = 'dbo' )
BEGIN
PRINT 'Changed DATABASE owner FROM ' + @LoginName
+ ' TO sa'
EXECUTE('USE [' + @dbName + '];EXEC sp_changedbowner ''sa''')
END
--If not owner of DB , proceed to Drop User
ELSE
BEGIN
PRINT 'Droping USER ' + @USER + ' IN Database:'
+ @DBName
SET @DropUSerSQL= 'DROP USER [' + @USER + ']'
EXECUTE('USE [' + @dbName + ']; EXEC sp_executesql N''' + @DropUSerSQL + '''')
END
--Fetch next DataBase name
FETCH NEXT FROM CUR INTO @DBName
END
--Close and Deallocate Cursor
CLOSE CUR
DEALLOCATE CUR
PRINT 'Dropping Login ' + @LoginName
SET @DropLoginSQL= 'DROP Login [' + @LoginName + ']'
EXECUTE(@DropLoginSQL)
END
ELSE
BEGIN
PRINT 'Login Does NOT EXISTS'
END
There is then going to be no reason for them to prove that what they are banning is actually hateful as they can just say that it is hateful and ban it before most people get the chance to take a deeper look or remove it with only a few people ever finding out about it.
ReplyDeleteJames Martin
i never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful. https://sites.google.com/site/hotmailloginonline/
ReplyDeleteIf more people that write articles involved themselves with writing great content like you, more readers would be interested in their writings. I have learned too many things from your article. entrar no hotmail
ReplyDeleteYour blog is too much amazing. I have found with ease what I was looking. Moreover, the content quality is awesome. Thanks for the nudge! traffic secrets
ReplyDelete