@if "%_echo%" == "" echo off


REM Make sure path to osql is set up correctly
if not exist "%SQLTOOLS%\osql.exe" echo %SamplesDirectory%\SetGlobalVars.cmd(92,35): error U1081: "%SQLTOOLS%\osql.exe" not found. The path to SQL tools in SetGlobalVars.cmd may be incorrect. > GrantPermissions.out 2>&1 && goto Error


REM Build the OSQL input file with all the permission changes we need

REM It is unnecessary to add the account to the database roles if
REM it is already the database owner.  Not only is it unncessary,
REM but It will cause errors since the user is already granted
REM access to the database but under the 'dbo' name.

echo Adding the subscription application account (%SubscriptionApplicationAccount%) to the appropriate database roles

echo DECLARE @ErrorCode INT                                                                      > "%TEMP%\GrantPermissions.tmp"
echo DECLARE @dbowner SYSNAME                                                                   >> "%TEMP%\GrantPermissions.tmp"


echo EXEC @ErrorCode = sp_grantlogin '%SubscriptionApplicationAccount%'                         >> "%TEMP%\GrantPermissions.tmp"


echo SELECT @dbowner = SUSER_SNAME(sid) FROM master.dbo.sysdatabases WHERE name = '%InstanceName%NSMain' >> "%TEMP%\GrantPermissions.tmp"
echo IF @dbowner ^<^> '%SubscriptionApplicationAccount%'                                        >> "%TEMP%\GrantPermissions.tmp"
echo BEGIN                                                                                      >> "%TEMP%\GrantPermissions.tmp"

echo USE %InstanceName%NSMain                                                                   >> "%TEMP%\GrantPermissions.tmp"
echo SELECT @ErrorCode = @@ERROR                                                                >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_grantdbaccess '%SubscriptionApplicationAccount%'                      >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'NSSubscriberAdmin', '%SubscriptionApplicationAccount%' >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo END                                                                                        >> "%TEMP%\GrantPermissions.tmp"


echo SELECT @dbowner = SUSER_SNAME(sid) FROM master.dbo.sysdatabases WHERE name = '%InstanceName%%AppName%' >> "%TEMP%\GrantPermissions.tmp"
echo IF @dbowner ^<^> '%SubscriptionApplicationAccount%'                                        >> "%TEMP%\GrantPermissions.tmp"
echo BEGIN                                                                                      >> "%TEMP%\GrantPermissions.tmp"

echo USE %InstanceName%%AppName%                                                                >> "%TEMP%\GrantPermissions.tmp"
echo SELECT @ErrorCode = @@ERROR                                                                >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_grantdbaccess '%SubscriptionApplicationAccount%'                      >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'NSSubscriberAdmin', '%SubscriptionApplicationAccount%' >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'NSEventProvider', '%SubscriptionApplicationAccount%'   >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo END                                                                                        >> "%TEMP%\GrantPermissions.tmp"


echo Adding the service account ("%ServiceSqlServerAccount%") to the appropriate roles

echo SELECT @dbowner = SUSER_SNAME(sid) FROM master.dbo.sysdatabases WHERE name = '%InstanceName%NSMain' >> "%TEMP%\GrantPermissions.tmp"
echo IF @dbowner ^<^> '%ServiceSqlServerAccount%'                                               >> "%TEMP%\GrantPermissions.tmp"
echo BEGIN                                                                                      >> "%TEMP%\GrantPermissions.tmp"

echo USE %InstanceName%NSMain                                                                   >> "%TEMP%\GrantPermissions.tmp"
echo SELECT @ErrorCode = @@ERROR                                                                >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_grantdbaccess '%ServiceSqlServerAccount%'                             >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'db_owner', '%ServiceSqlServerAccount%'                 >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'NSRunService', '%ServiceSqlServerAccount%'             >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo END                                                                                        >> "%TEMP%\GrantPermissions.tmp"


echo SELECT @dbowner = SUSER_SNAME(sid) FROM master.dbo.sysdatabases WHERE name = '%InstanceName%%AppName%' >> "%TEMP%\GrantPermissions.tmp"
echo IF @dbowner ^<^> '%ServiceSqlServerAccount%'                                               >> "%TEMP%\GrantPermissions.tmp"
echo BEGIN                                                                                      >> "%TEMP%\GrantPermissions.tmp"

echo USE %InstanceName%%AppName%                                                                >> "%TEMP%\GrantPermissions.tmp"
echo SELECT @ErrorCode = @@ERROR                                                                >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_grantdbaccess '%ServiceSqlServerAccount%'                             >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'db_owner', '%ServiceSqlServerAccount%'                 >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo EXEC @ErrorCode = sp_addrolemember 'NSRunService', '%ServiceSqlServerAccount%'             >> "%TEMP%\GrantPermissions.tmp"
echo IF @ErrorCode ^<^> 0 GOTO ErrorExit                                                        >> "%TEMP%\GrantPermissions.tmp"

echo END                                                                                        >> "%TEMP%\GrantPermissions.tmp"

echo ErrorExit:                                                                                 >> "%TEMP%\GrantPermissions.tmp"
echo EXIT(SELECT @ErrorCode)                                                                    >> "%TEMP%\GrantPermissions.tmp"


REM If we're not using integrated security then prompt for the SQL user's password

echo Running OSQL with the following flags: -S%SqlServer% %SqlLoginFlags%

if not "%SqlLoginFlags%"=="-E" (
	echo.
	echo Enter the SQL Server password for user %SqlUserName%:
)

"%SQLTOOLS%\osql" -S%SqlServer% %SqlLoginFlags% -i "%TEMP%\GrantPermissions.tmp" > GrantPermissions.out 2>&1
if %ERRORLEVEL% NEQ 0 goto Error

type GrantPermissions.out

del "%TEMP%\GrantPermissions.tmp" > nul 2>&1

:End
exit /B 0

:Error
del "%TEMP%\GrantPermissions.tmp" > nul 2>&1

REM Dump the output.  If this is run as a shell from another script it will be
REM dumped there, too.  But if this is run independantly we want to report errors.
type GrantPermissions.out

exit /B 1

