Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Audit SQL Logins - Updated Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2007 7:33 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 8, Visits: 45
Comments posted to this topic are about the item Audit SQL Logins - Updated


Thanks,

~DH
Post #402799
Posted Monday, October 1, 2007 10:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 8, 2008 11:01 AM
Points: 93, Visits: 73
Thanks for this script.
Post #405145
Posted Thursday, October 4, 2007 8:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2008 5:24 PM
Points: 3, 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...
Post #407170
Posted Tuesday, October 9, 2007 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 4, 2010 4:52 PM
Points: 4, 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 '?'.
Post #408631
Posted Saturday, October 20, 2007 2:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 8:34 AM
Points: 1, Visits: 61
I get the same syntax errors, anyone know what they are? I can't see them
Post #413108
Posted Wednesday, October 24, 2007 4:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 25, 2010 1:43 PM
Points: 39, Visits: 81
I got the same errors, but when I retyped out the commands it worked fine.
Post #414674
Posted Wednesday, October 24, 2007 4:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 25, 2010 1:43 PM
Points: 39, 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
Post #414683
Posted Thursday, January 24, 2008 3:42 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, March 2, 2014 4:34 PM
Points: 724, Visits: 1,001
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.
Post #447237
Posted Thursday, March 27, 2008 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 29, 2009 9:32 AM
Points: 1, 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!
Post #475378
Posted Monday, July 7, 2008 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2008 3:16 AM
Points: 1, 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 ' '.
Post #529117
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse