February 29, 2008 at 5:19 am
I am trying to create a script that reports on security issues and creates a script that can be appied to fix them. I am struggling with part of it.
SET NOCOUNT ON
/* -----------------------------------------------------------------------------
12.Look for Orphan users and drop then
-------------------------------------------------------------------------------- */
PRINT ''
PRINT '/*'
PRINT 'Script to drop orphaned users, this has been left commented out, but you may'
PRINT 'wish to consider running some of these'
PRINT ''
DECLARE @Database sysname, @NSQL NVARCHAR(MAX), @SQL4 VARCHAR(MAX),
@SQL VARCHAR(MAX),@i INT
SET @Database = 'crime2'
CREATE TABLE #Report
(SysAction VARCHAR(35),
SysObject VARCHAR(35),
[Action] VARCHAR(45),
Reason VARCHAR(45))
CREATE TABLE #Orphans
(UserName VARCHAR (50),
UserSID VARCHAR (100),
createdate DATETIME NULL,
updatedate DATETIME NULL)
SET @SQL4 = 'USE [' + @database + '] INSERT INTO #Orphans '
SET @SQL4 = @SQL4 +'(UserName,UserSID) '
SET @SQL4 = @SQL4 +'EXEC (sp_change_users_login ''report'')'
EXEC @SQL4
UPDATE #Orphans
SET createdate = S.createdate, updatedate = S.updatedate
FROM sysusers S
INNER JOIN #Orphans O ON O.UserSID = S.sid
SET @NSQL = N'SELECT @i = COUNT(*) FROM #Orphans'
EXEC sp_executesql
@query = @NSQL,
@params = N'@i INT OUTPUT',
@i = @i OUTPUT
IF @i <> 0 --Orphan exists
BEGIN
SET @SQL = ('INSERT INTO #Report (SysAction,SysObject,[Action],Reason)
SELECT ''DATABASE_USER'',''''+ [UserName] +'''',''User Created ''+CONVERT(varchar(10), createdate, 103),''Drop User''
FROM #Orphans')
EXEC (@SQL)
END
ELSE
BEGIN
PRINT ''
-- No orphans found so no need to add record to the report
END
/*Script to delete Orphan Users */
PRINT ''
PRINT '/*'
SELECT 'EXEC sp_revokedbaccess '''+ [UserName]+''''
FROM #Orphans
PRINT '*/'
DROP TABLE #Orphans
DROP TABLE #Report
The result gives me the following error:-
Msg 203, Level 16, State 2, Line 33
The name 'USE [crime2] INSERT INTO #Orphans (UserName,UserSID) EXEC (sp_change_users_login 'report')' is not a valid identifier.
Any help would be appreciated.
February 29, 2008 at 7:15 am
Try putting a ; at the end of the USE statement.
Meaning -
... Use [crime2]; ...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 29, 2008 at 7:17 am
Your USE DATABASE statement has to be it's own line, not inline with the update.
Running the following:
USE DATABASE SELECT * FROM TABLE
Gets me the error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DATABASE'.
However, running this works fine:
USE DATABASE
SELECT * FROM TABLE
You need to separate the USE command from the Insert.
Regards, Irish
February 29, 2008 at 7:56 am
Jeffrey Irish (2/29/2008)
Your USE DATABASE statement has to be it's own line, not inline with the update.Running the following:
USE DATABASE SELECT * FROM TABLE
Gets me the error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DATABASE'.
However, running this works fine:
USE DATABASE
SELECT * FROM TABLE
You need to separate the USE command from the Insert.
I am not so sure about this. I just tried it and I get results without any error.
-Roy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply