September 25, 2007 at 7:33 pm
Comments posted to this topic are about the item Audit SQL Logins - Updated
~DH
October 1, 2007 at 10:57 am
Thanks for this script.
October 4, 2007 at 8:27 pm
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...
October 9, 2007 at 12:19 pm
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 '?'.
October 20, 2007 at 2:17 pm
I get the same syntax errors, anyone know what they are? I can't see them
October 24, 2007 at 4:21 pm
I got the same errors, but when I retyped out the commands it worked fine.
October 24, 2007 at 4:51 pm
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
January 24, 2008 at 3:42 pm
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.
March 27, 2008 at 7:17 am
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!
July 7, 2008 at 2:55 am
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 '?'.
August 21, 2008 at 12:10 pm
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.
December 24, 2011 at 9:23 pm
Can some one provide me the solution..as I am receiving the following error;
Must declare the scalar variable "@DBName" and
Must declare the scalar variable "@SQLCmd".
I am receiving this error during cursor process. Please find some solution. Thank you
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply