SQL ‐ Script to add IIS APP Pool user in SQL Server Database
IIS is Internet Information Services in which one could host OpenRose API and OpenRose WebUI projects.
OpenRose API connect to the SQL Server database mainly for CRUD (Create, Read, Update and Delete) operations. For this purpose, IIS user has to be authenticated in SQL Server. To be able to do that, Microsoft provides support for adding IIS App Pool user into SQL Server Database as Windows Identity.
Following script is designed to help users to create IIS App Pool account to be added first into SQL Server and then as database owner for OpenRose database. This way, It's possible to use this user to establish communication between OpenRose API and SQL Server OpenRose Database.
NOTE : We have tested below scripts in our test environment and it's provided on an "As Is" basis. OpenRose team does not guarantee it's workings and outcomes. Person running script script in SQL Server has to have sysadmin and dbowner role for SQL Server and the Database respectively
-- SCRIPT TO ADD IIS APP POOL USER TO SQL SERVER AND DATABASE
-- SQL Server Script Parameters
DECLARE @AppPoolName NVARCHAR(255) = 'IIS APPPOOL\IGNOREME'
DECLARE @DatabaseName NVARCHAR(255) = 'OpenRoseDB'
DECLARE @ErrorMessage NVARCHAR(4000)
-- Lets try and create use in SQL Server itself
BEGIN TRY
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @AppPoolName)
BEGIN
EXEC sp_grantlogin @AppPoolName
PRINT 'Login created.'
END
ELSE
BEGIN
PRINT 'Login already exists.'
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
PRINT 'Error creating login: ' + @ErrorMessage
END CATCH
-- Lets try and create use in SQL Server Database
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
USE [' + @DatabaseName + '];
BEGIN TRY
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @AppPoolName + ''')
BEGIN
CREATE USER [' + @AppPoolName + '] FOR LOGIN [' + @AppPoolName + '];
PRINT ''User created in the database.'';
END
ELSE
BEGIN
PRINT ''User already exists in the database.'';
END;
ALTER ROLE [db_owner] ADD MEMBER [' + @AppPoolName + '];
PRINT ''User added to db_owner role.'';
END TRY
BEGIN CATCH
PRINT ''Error in the database operation: '' + ERROR_MESSAGE();
END CATCH;'
EXEC sp_executesql @SQL
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Following script was designed to remove IIS App Pool account from the database and then from the server itself.
DANGER : This script will remove IIS APP Pool from the database as well as from SQL Server itself.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- SCRIPT TO DELETE IIS APP POOL USER FROM SQL SERVER
-- Parameters
DECLARE @AppPoolName NVARCHAR(255) = 'IIS APPPOOL\IGNOREME'
DECLARE @DatabaseName NVARCHAR(255) = 'OpenRoseDB'
DECLARE @ErrorMessage NVARCHAR(4000)
-- Lets try and remove use in SQL Server Database
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
USE [' + @DatabaseName + '];
BEGIN TRY
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @AppPoolName + ''')
BEGIN
DROP USER [' + @AppPoolName + '];
PRINT ''User dropped from the database.'';
END
ELSE
BEGIN
PRINT ''User does not exist in the database.'';
END
END TRY
BEGIN CATCH
PRINT ''Error in the database operation: '' + ERROR_MESSAGE();
END CATCH;'
EXEC sp_executesql @SQL
-- Lets try and remove use in SQL Server itself
BEGIN TRY
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @AppPoolName)
BEGIN
EXEC('DROP LOGIN [' + @AppPoolName + ']');
PRINT 'Login dropped.';
END
ELSE
BEGIN
PRINT 'Login does not exist.';
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
PRINT 'Error dropping login: ' + @ErrorMessage
END CATCH
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Comments
Post a Comment