SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit SQL Logins - Updated


Audit SQL Logins - Updated

Author
Message
Derrick H.
Derrick H.
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 57
Comments posted to this topic are about the item Audit SQL Logins - Updated


Thanks,

~DH
Duquene Jerome
Duquene Jerome
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 73
Thanks for this script.
rachel.lee
rachel.lee
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

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...
ntdoc
ntdoc
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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 '?'.


Robert J Miller
Robert J Miller
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 100
I get the same syntax errors, anyone know what they are? I can't see them
Ryan Austin
Ryan Austin
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 81
I got the same errors, but when I retyped out the commands it worked fine.
Ryan Austin
Ryan Austin
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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
TRACEY-320982
TRACEY-320982
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1736 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.
Nicholas Howard Kinney
Nicholas Howard Kinney
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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!
guguman07
guguman07
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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 ' '.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search