OSQL - Running Queries in a Batch File

OSQL is another very useful tool I and my group use very often.We used it to execute all the sql files in a folder against a given server and database.

Here is the batch file I use to check if all the user procedures and user defined functions in a database has a execute persmission for a user ROLE called 'APPUSER'.

This sample batch file also demonstrates how to execute sql queries using OSQL in a batch file.


@ECHO OFF
:: Keep variables local
SETLOCAL

SET Query="select so.name from sys.objects so LEFT OUTER JOIN sys.database_permissions p on so.[object_id] = p.major_id inner JOIN sys.database_principals dp on dp.name = 'APPUSER' where so.Type IN ('P') and p.major_id is null"

ECHO ^>^> Running CheckIfAPPUSERIsGrantedExecuteOnAllProcs
ECHO ^>^> ----------------------------------------------
:serverset server=
set /P server=^>^> Enter Server Name or Type exit to Exit: %=%

if "%server%"=="" goto serverif "%server%"=="exit"
goto End

:databaseset database=
set /P database=^>^> Enter Database Name or Type exit to Exit: %=%
if "%database%"=="" goto databaseif "%database%"=="exit"
goto End

:: Execute sql scripts
ECHO ^>^> Checking . . . Please Wait!
OSQL -E -S %1 -d %2 -n -m-1 -b -Q %Query%

IF ERRORLEVEL 1
(
ECHO ^>^> Critcal error while checking Database %2,Ending Checking.
GOTO End
)

:: Display complete message
ECHO Check Complete on database %2
:: Done
GOTO End

:End
ENDLOCAL
pause
exit

Comments

Popular posts from this blog

Duplicate Folders Freeware - Not Duplicate Files

SSRS Compile Errors

Free SQL Server Management Studio Addin/Plugin/External Tool to get object information quickly