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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy