/* The following set of queries return results that can be used for automating a SQL Server audit
   Author:	Cindy Soule
   Date:	2/9/2004

To get results in text mode turn the execute mode to results in text in your
SQL Query Analyzer window.

To customize to your environment  ----
Do a find on 'Northwind' and replace the word 'Northwind' with the name of the database you are auditing.

If you are auditing multiple databases on the same server repeat the sections with 'Use database' once for each
database you are auditing.
*/


Set Nocount on  --- turns off select statistics 
go



PRINT '******************The SQL Server Version as of *******************************'
select getdate()  -- getdate function returns current date
Select @@ServerName
Select @@Version
PRINT '********************End SQL Server Version Information ************************'
GO


PRINT '******************The SQL Server Configuration Settings as of *******************************'
select getdate()  -- getdate function returns current date
Exec sp_configure
PRINT '********************End SQL Server Configuration Settings **********************************'
GO



PRINT '******************The Database Configuration Settings as of *******************************'
select getdate()  -- getdate function returns current date
EXEC sp sp_helpdb   --- returns database name general database information     	    --- replace database name
EXEC sp_dboption 'Northwind'	-- returns specific database configuration settings --- replace database name
PRINT '********************End Database Configuration Settings  **********************************'
GO


PRINT '******************The SQL Server Logins as of *******************************'
select getdate()  -- getdate function returns current date
PRINT 'Logins for Server'select @@SERVERNAME
EXEC sp_helplogins	-- gives logins and server role and database role for each login on server
PRINT '********************End Database Configuration Settings  **********************************'
GO



PRINT '******************The Database Roles as of *******************************'
select getdate()  -- getdate function returns current date
PRINT 'Roles for Database Northwind'
Use Northwind								          ---replace database name here
EXEC sp_helprole	-- provides list of user defined roles
Print 'User Logins Associated with Roles'
EXEC sp_helprolemember	-- provides list of user logins associated with role
PRINT '********************End Database Configuration Settings  **********************************'
GO


/* If you want to find explicit permissions associated to a database object 
Run the below script section once for every database object that you are sampling. 
Replace 'EnterObjectName' with object name  -- for example Exec ..sp_helprotect 'Employees'
If you want multiple object results you will need to run the execute command once for each object.
*/

PRINT '*************************Permission Associated with Objects as of **********************'
select getdate()  -- getdate function returns current date
exec ..sp_helprotect 'EnterObjectName '				--- replace EnterObjectName with actual object name
PRINT '********************End Permission Associated with Objects  **********************************'
GO

Set Nocount Off  --- turns off select statistics 
go