Audit SQL Logins - Updated

  • Derrick H.

    SSC Enthusiast

    Points: 164

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


    Thanks,

    ~DH

  • Duquene Jerome

    SSC Enthusiast

    Points: 187

    Thanks for this script.

  • rachel.lee

    SSC Rookie

    Points: 39

    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

    Valued Member

    Points: 58

    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

    Grasshopper

    Points: 21

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

  • Ryan Austin

    Old Hand

    Points: 373

    I got the same errors, but when I retyped out the commands it worked fine.

  • Ryan Austin

    Old Hand

    Points: 373

    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

    SSChampion

    Points: 13647

    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

    SSC Journeyman

    Points: 75

    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

    SSC Journeyman

    Points: 81

    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 '?'.

  • arenzja

    Ten Centuries

    Points: 1206

    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.

  • DBA_Learner

    SSCarpal Tunnel

    Points: 4228

    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