Audit SQL Logins

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Derrick H. » Audit SQL Logins - Updated

12 posts, Page 1 of 2

1 2 »»

Audit SQL Logins - Updated

Rate Topic

Display Mode

Topic Options

Author

Message

Derrick H.

Posted Tuesday, September 25, 2007 7:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:20 AM
Points: 8, Visits: 45

Comments posted to this topic are about the item Audit SQL Logins - Updated

Thanks,

~DH

Post #402799

Duquene Jerome

Posted Monday, October 1, 2007 10:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, December 8, 2008 11:01 AM
Points: 93, Visits: 73

Thanks for this script.

Post #405145

rachel.lee

Posted Thursday, October 4, 2007 8:27 PM

Forum 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

ntdoc

Posted Tuesday, October 9, 2007 12:19 PM

Forum 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

Robert J Miller

Posted Saturday, October 20, 2007 2:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 6, 2015 1:35 PM
Points: 1, Visits: 73

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

Post #413108

Ryan Austin

Posted Wednesday, October 24, 2007 4:21 PM

SSC 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

Ryan Austin

Posted Wednesday, October 24, 2007 4:51 PM

SSC 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

TRACEY-320982

Posted Thursday, January 24, 2008 3:42 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, January 7, 2015 2:54 PM
Points: 725, Visits: 1,003

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

Nicholas Howard Kinney

Posted Thursday, March 27, 2008 7:17 AM

Forum 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

guguman07

Posted Monday, July 7, 2008 2:55 AM

Forum 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 »

12 posts, Page 1 of 2

1 2 »»

Permissions