SQL SERVER DATABASE SECURITY & USER REVIEW

Following pasted into a Batch file will extract information from SQL Database
You need to have SA priveledges to the SQL Dbase to run.

--->START SCRIPT<---
REM Replace % Server_Name % with ServerName
REM Replace % OUTPUT_PATH % with Output location
REM Replace % DB_Review % with Database name

ECHO -- Obtain all logins from the database
OSQL -E -S %Server_Name% -Q "use master select * from master.dbo.syslogins" -s "," -w2000 -E -o %OUTPUT_PATH%\syslogins.txt

ECHO -- Obtain patch version
OSQL -E -S %Server_Name% -Q "select @@version" -s "," -w2000 -E -o %OUTPUT_PATH%\version.txt

ECHO -- Obtain names of databases defined within the SQL server instance
OSQL -E -S %Server_Name% -Q "select name from master.dbo.sysdatabases" -s "," -w2000 -E -o %OUTPUT_PATH%\active_db.txt

ECHO -- Obtain users from database under analysis
OSQL -E -S %Server_Name% -Q "use %DB_REVIEW% select uid, name, createdate, updatedate, hasdbaccess, islogin, isntname, isntgroup, isntuser, issqluser, isaliased, issqlrole, isapprole from sysusers where islogin = 1" -s "," -w2000 -E -o %OUTPUT_PATH%\db_users.txt

ECHO -- Obtain users from master database
OSQL -E -S %Server_Name% -Q "use master select uid, name, createdate, updatedate, hasdbaccess, islogin, isntname, isntgroup, isntuser, issqluser, isaliased, issqlrole, isapprole from sysusers where islogin = 1" -s "," -w2000 -E -o %OUTPUT_PATH%\master_users.txt

ECHO -- Obtain authentication mode for the sql_server instance
OSQL -E -S %Server_Name% -Q "master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key='%Reg_address%',@value_name='LoginMode'" -s "," -w2000 -E -o %OUTPUT_PATH%\Authen_mode.txt

ECHO -- Obtain advanced option parameters for sql server instance
OSQL -E -S %Server_Name% -Q "USE master EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE"
OSQL -E -S %Server_Name% -Q "master..sp_configure" -s "," -w2000 -E -o %OUTPUT_PATH%\configuration.txt

ECHO -- Obtain audit level being used
OSQL -E -S %Server_Name% -Q "master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key='%Reg_address%',@value_name='AuditLevel'" -s "," -w2000 -E -o %OUTPUT_PATH%\Audit_level.txt

ECHO -- Obtain default login being used for NT authentication
OSQL -E -S %Server_Name% -Q "master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key='%Reg_address%',@value_name='DefaultLogin'" -s "," -w2000 -E -o%OUTPUT_PATH%\Default_logon.txt

ECHO -- Obtain membership of all fixed server roles
OSQL -E -S %Server_Name% -Q "master..sp_helpsrvrolemember" -s "," -w2000 -E -o %OUTPUT_PATH%\srvrolemember.txt

ECHO -- Obtain all database roles (application and database) in database under review
OSQL -E -S %Server_Name% -Q "%DB_REVIEW%..sp_helprole" -s "," -w2000 -E -o %OUTPUT_PATH%\DB_and_App_roles.txt

ECHO -- Obtain membership of all fixed and custom database roles
OSQL -E -S %Server_Name% -Q "%DB_REVIEW%..sp_helprolemember" -s "," -w2000 -E -o %OUTPUT_PATH%\DB_roles.txt

ECHO -- Obtain permissions on stored procedures and tables in database under review
OSQL -E -S %Server_Name% -Q "%DB_REVIEW%..sp_helprotect" -s "," -w2000 -E -o %OUTPUT_PATH%\permissions_DB.txt

ECHO -- Obtain permissions on stored procedures and tables in master
OSQL -E -S %Server_Name% -Q "master..sp_helprotect" -s "," -w2000 -E -o %OUTPUT_PATH%\permissions_master.txt

ECHO -- Obtain orphaned users in database under review
OSQL -E -S %Server_Name% -Q "%DB_REVIEW%..sp_change_users_login @Action='Report'" -s "," -w2000 -E -o %OUTPUT_PATH%\orphaned_users.txt

ECHO Extraction complete
PAUSE
--->END SCRIPT<---