Group: General Forum Members
Points: 114
Visits: 62
|
Comments posted to this topic are about the item Audit SQL Logins - Updated
Thanks, ~DH
|
Group: General Forum Members
Points: 97
Visits: 73
|
Thanks for this script.
|
Group: General Forum Members
Points: 9
Visits: 16
|
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...
|
Group: General Forum Members
Points: 12
Visits: 18
|
SQL 2005 w/SP2 gives the following errors Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 54
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 114
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 135
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 162
Incorrect syntax near '?'.
|
Group: General Forum Members
Points: 7
Visits: 100
|
I get the same syntax errors, anyone know what they are? I can't see them
|
Group: General Forum Members
Points: 267
Visits: 81
|
I got the same errors, but when I retyped out the commands it worked fine.
|
Group: General Forum Members
Points: 267
Visits: 81
|
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 ##Users ORDER BY [Login Type],[AD Login Type],[Login Name] -- *************************************************************************** -- *************************************************************************** -- Create the output table for the Database User ID's CREATE 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 server DECLARE csrDB CURSOR FOR SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb'); -- *************************************************************************** -- *************************************************************************** -- Open the cursor and get the first database name OPEN csrDB FETCH NEXT FROM csrDB INTO @DBName -- *************************************************************************** -- *************************************************************************** -- Loop through the cursor WHILE @@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 CURSOR CLOSE csrDB DEALLOCATE csrDB -- *************************************************************************** -- *************************************************************************** -- Return the Database User data SELECT * FROM ##DBUsers ORDER BY [Database User ID],[Database]; -- *************************************************************************** -- *************************************************************************** -- Clean up - delete the Global temp tables IF 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
|
Group: General Forum Members
Points: 8912
Visits: 1014
|
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.
|
Group: General Forum Members
Points: 63
Visits: 11
|
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!
|
Group: General Forum Members
Points: 69
Visits: 2
|
i've received an error on slq 2005 sp2 Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 54 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 114 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 135 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Procedure usp_sql_audit, Line 162 Incorrect syntax near ' '.
|