September 21, 2016 at 2:37 pm
Hi All,
I'm attempting to make a script which will pull in a list of all unauthorized DB users (and their corresponding databases) on a single server instance and then drop said users in one go. I made an almost-identical script for logins that worked well, the only difference being that logins are server-wide, so I didn't need to use SP_MSforeachDB and didn't need to gather any DB info. However, I eventually got that script to work, and this one doesn't. I've run out of ideas as to why. It returns several (identical) syntax error messages, none of which are particularly helpful because when I go to look at the supposed syntax error, I can't find it.
I think perhaps I have been staring at it too long and am missing something obvious. Does anyone notice something amiss? Or have a simpler/more elegant way to do this?
Here is the affected script. For testing purposes, I switched out 'DROP USER [' + @NextUser +'];' with 'PRINT ' +@NextUser+';', but I don't think this is the cause of the problem.
/* Declare error-handling variables */
DECLARE @err_no int, @err_severity int, @err_state int, @err_line int, @err_message varchar(4000)
DECLARE @nrows int
CREATE TABLE #DroppedUsers (DatabaseName VARCHAR(100), UserName VARCHAR(100));
EXECUTE master.sys.sp_MSforeachdb
'USE [?];
INSERT INTO #DroppedUsers (DatabaseName,UserName)
SELECT DB_NAME(), name FROM sys.database_principals
WHERE
name LIKE ''COMPANY\%''
AND
name NOT IN (''COMPANY\User1'',''COMPANY\User2'',''COMPANY\User3'')'
BEGIN TRY
DECLARE @Fetch_Status_User INT;
DECLARE @NextUser VARCHAR(100);
DECLARE @NextDatabase VARCHAR(100);
DECLARE @DropUser_SQL VARCHAR(250);
DECLARE UserList CURSOR FOR
SELECT DatabaseName,UserName FROM #DroppedUsers
OPEN UserList
FETCH NEXT FROM UserList INTO @NextDatabase, @NextUser
SET @Fetch_Status_User = @@FETCH_STATUS
WHILE @Fetch_Status_User = 0
BEGIN
SET @DropUser_SQL = 'USE '+ @NextDatabase +';'+ CHAR(10) + 'PRINT ' + @NextUser + ';'
EXEC (@DropUser_SQL)
SELECT @NextUser,'has been dropped.'
FETCH NEXT FROM UserList INTO @NextUser
SET @Fetch_Status_User=@@FETCH_STATUS
END
CLOSE UserList
DEALLOCATE UserList
END TRY
BEGIN CATCH
SELECT @err_no=ERROR_NUMBER(), @err_severity=ERROR_SEVERITY(), @err_state=ERROR_STATE(),
@err_line=ERROR_LINE(), @err_message=ERROR_MESSAGE()
GOTO ERROR_EXIT
END CATCH
DROP TABLE #DroppedUsers
GOTO BAGIT
/* ********************************************************************************** */
/* Handle error conditions */
/* ********************************************************************************** */
ERROR_EXIT:
DECLARE @newline char(1)
SET @newline = CHAR(10)
PRINT 'Error in Redacted.sql'
RAISERROR('Error Number: %d, Severity: %d, State: %d, Line: %d, %s%s', 15, 1,
@err_no, @err_severity, @err_state, @err_line, @newline, @err_message) WITH LOG;
BAGIT:
GO
Here is the script I finally got to work, for the logins:
/* Declare error-handling variables */
DECLARE @err_no int, @err_severity int, @err_state int, @err_line int, @err_message varchar(4000)
DECLARE @nrows int
BEGIN TRY
DECLARE @Fetch_Status_Login INT;
DECLARE @NextLogin VARCHAR(100);
DECLARE @DropLogin_SQL VARCHAR(250);
DECLARE LoginList CURSOR FOR
SELECT name FROM sys.server_principals
WHERE
name LIKE 'COMPANY\%'
AND
name NOT IN ('COMPANY\User1','COMPANY\User2','COMPANY\User3')
OPEN LoginList
FETCH NEXT FROM LoginList INTO @NextLogin
SET @Fetch_Status_Login = @@FETCH_STATUS
WHILE @Fetch_Status_Login = 0
BEGIN
SET @DropLogin_SQL = 'DROP LOGIN [' + @NextLogin + '];'
EXEC (@DropLogin_SQL)
SELECT @NextLogin,'has been dropped.'
FETCH NEXT FROM LoginList INTO @NextLogin
SET @Fetch_Status_Login=@@FETCH_STATUS
END
CLOSE LoginList
DEALLOCATE LoginList
END TRY
BEGIN CATCH
SELECT @err_no=ERROR_NUMBER(), @err_severity=ERROR_SEVERITY(), @err_state=ERROR_STATE(),
@err_line=ERROR_LINE(), @err_message=ERROR_MESSAGE()
GOTO ERROR_EXIT
END CATCH
GOTO BAGIT
/* **************************************** */
/* Handle error conditions */
/* **************************************** */
ERROR_EXIT:
DECLARE @newline char(1)
SET @newline = CHAR(10)
PRINT 'Error in Redacted.sql'
RAISERROR('Error Number: %d, Severity: %d, State: %d, Line: %d, %s%s', 15, 1,
@err_no, @err_severity, @err_state, @err_line, @newline, @err_message) WITH LOG;
BAGIT:
GO
September 21, 2016 at 4:53 pm
And what error message(s) are you getting?
Please provide the complete error message(s).
September 22, 2016 at 7:11 am
Whoops, sorry about that! Knew I was forgetting something. The error is as follows:
Error in Redacted.sql
Msg 50000, Level 15, State 1, Line 60
Error Number: 102, Severity: 15, State: 1, Line: 2,
Incorrect syntax near '\'.
September 22, 2016 at 7:39 am
Replace the EXEC with PRINT, to print the query to the messages tab and post the resulting dynamic SQL output?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 7:52 am
Fantastic! Thank you! Question though, how do I convert this over to actually dropping the users while still maintaining the current database? I eventually want to execute the statement as USE @NextDatabase; DROP USER @NextUser. I have it set to PRINT in the example code for testing purposes, but eventually I don't want to print, I want to drop.
September 22, 2016 at 8:01 am
Your print statement generates PRINT Company\user, should probably be PRINT 'Company\user'
Try 'PRINT ''' +@NextUser+''';',
As Gail mentioned testing with print or select before Exec would help you fix these types of issues.
September 22, 2016 at 8:12 am
I think I get it now! Thanks for the tips, everyone!
September 22, 2016 at 12:50 pm
lmk1 (9/22/2016)
I have it set to PRINT in the example code for testing purposes, but eventually I don't want to print, I want to drop.
The PRINT is just so you can debug the dynamic SQL, it's not a fix, it's so you can see what fix is necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply