SQLServerCentral / Article Discussions / Discuss content posted by Derrick H. / Article Discussions by Author / Audit SQL Logins - Updated / Latest Posts

RE: Audit SQL Logins - Updated

DBA_Learner — Sat, 24 Dec 2011 21:23:56 GMT

Can some one provide me the solution..as I am receiving the following error;Must declare the scalar variable "@DBName" andMust declare the scalar variable "@SQLCmd".I am receiving this error during cursor process. Please find some solution. Thank you

RE: Audit SQL Logins - Updated

arenzja — Thu, 21 Aug 2008 12:10:08 GMT

Was having the same issue as guguman07, put the script into notepad and found a bunch of bad characters. Once I removed those it worked perfect.

RE: Audit SQL Logins - Updated

guguman07 — Mon, 07 Jul 2008 02:55:17 GMT

i've received an error on slq 2005 sp2Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 54Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 114Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 135Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 162Incorrect syntax near ' '.

RE: Audit SQL Logins - Updated

Nicholas Howard Kinney — Thu, 27 Mar 2008 07:17:29 GMT

I am new to this whole procedure but I have around a year of SQL knowledge. I get this error,"The identifier that starts with ' AS [Database] FROM [AdultCSODataSQL].[dbo].[sysusers] su LEFT OUTER JOIN ##Users u ON su.sid = u.sid LEFT OUTER JOIN ([AdultCSO' is too long. Maximum length is 128."Thanks in advance while I debug!

RE: Audit SQL Logins - Updated

TRACEY-320982 — Thu, 24 Jan 2008 15:42:16 GMT

hi there i am changing all my NT Accounts to a new domain\username.Do you have a script for SQL 2005 that would generate the creation.So if i have olddomain\username that has access to databasea, databaseb and the permissions of read write I was trying to figure out if i could then create new accounts with these permissions But if i have script then have to do manual creations of accounts.Any thoughts be appreciated.

RE: Audit SQL Logins - Updated

Ryan Austin — Wed, 24 Oct 2007 16:51:26 GMT

I made some other changes that made this work a little better for me, hopefully it helps.Biggest thing that I did notice was that the [Default Database] was set to a varchar and I changed that back to sysname.CREATE TABLE ##Users ( [sid] varbinary(85) NULL, [Login Name] nvarchar(128) NULL, [Default Database] sysname NULL, [Login Type] varchar(9), [AD Login Type] varchar(8), [sysadmin] varchar(3), [securityadmin] varchar(3), [serveradmin] varchar(3), [setupadmin] varchar(3), [processadmin] varchar(3), [diskadmin] varchar(3), [dbcreator] varchar(3), [bulkadmin] varchar(3))---------------------------------------------------------INSERT INTO ##Users SELECT sid, loginname AS [Login Name], dbname AS [Default Database], CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login Type], CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type], CASE [sysadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin], CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin], CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin], CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin], CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin], CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin], CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator], CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin]FROM master.dbo.syslogins;---------------------------------------------------------SELECT [Login Name], [Default Database], [Login Type], [AD Login Type], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin]FROM ##UsersORDER BY [Login Type],[AD Login Type],[Login Name]-- ***************************************************************************-- ***************************************************************************-- Create the output table for the Database User ID'sCREATE TABLE ##DBUsers ( [Database User ID] nvarchar(1024), [Server Login] nvarchar(1024), [Database Role] nvarchar(1024), [Database] sysname)-- ***************************************************************************-- ***************************************************************************-- Declare a cursor to loop through all the databases on the serverDECLARE csrDB CURSOR FOR SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');-- ***************************************************************************-- ***************************************************************************-- Open the cursor and get the first database nameOPEN csrDBFETCH NEXT FROM csrDB INTO @DBName-- ***************************************************************************-- ***************************************************************************-- Loop through the cursorWHILE @@FETCH_STATUS = 0 BEGIN-- ***************************************************************************-- ***************************************************************************-- SELECT @SQLCmd = 'INSERT INTO ##DBUsers SELECT su.[Name] as [Database User ID], COALESCE (u.[Login Name], ''**Orphaned**'') as [Server Login], COALESCE (sug.[name], ''Public'') AS [Database Role], ''' + @DBName + ''' as [Database] FROM [' + @DBName + '].[dbo].[sysusers] su LEFT OUTER JOIN ##Users u ON su.sid = u.sid LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ON sm.groupuid = sug.uid) ON su.uid = sm.memberuid WHERE su.hasdbaccess = 1 AND su.[name] != ''dbo'' ' EXEC (@SQLCmd)-- ***************************************************************************-- ***************************************************************************-- Get the next database name FETCH NEXT FROM csrDB INTO @DBName-- ***************************************************************************-- ***************************************************************************-- End of the cursor loop END-- ***************************************************************************-- ***************************************************************************-- Close and deallocate the CURSORCLOSE csrDBDEALLOCATE csrDB-- ***************************************************************************-- ***************************************************************************-- Return the Database User dataSELECT * FROM ##DBUsers ORDER BY [Database User ID],[Database];-- ***************************************************************************-- ***************************************************************************-- Clean up - delete the Global temp tablesIF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U')) DROP TABLE ##Users;IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U')) DROP TABLE ##DBUsers;-- ***************************************************************************GO

RE: Audit SQL Logins - Updated

Ryan Austin — Wed, 24 Oct 2007 16:21:21 GMT

I got the same errors, but when I retyped out the commands it worked fine.

RE: Audit SQL Logins - Updated

Robert J Miller — Sat, 20 Oct 2007 14:17:29 GMT

I get the same syntax errors, anyone know what they are? I can't see them

RE: Audit SQL Logins - Updated

ntdoc — Tue, 09 Oct 2007 12:19:22 GMT

SQL 2005 w/SP2 gives the following errors[quote]Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 54Incorrect syntax near '?'.Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 114Incorrect syntax near '?'.Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 135Incorrect syntax near '?'.Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 162Incorrect syntax near '?'.[/quote]

RE: Audit SQL Logins - Updated

rachel.lee — Thu, 04 Oct 2007 20:27:23 GMT

Hi,I ran this script on one of my test servers and it gave me rows saying I had orphans, but the database user was actually mapped to a windows authenticated login...

RE: Audit SQL Logins - Updated

Duquene Jerome — Mon, 01 Oct 2007 10:57:30 GMT

Thanks for this script.

Audit SQL Logins - Updated

Derrick H. — Tue, 25 Sep 2007 19:33:04 GMT

Comments posted to this topic are about the item [B]Audit SQL Logins - Updated[/B]