1. Successfully created login1 by applying the below manifest:
sqlserver::config{ 'MSSQLSERVER':
|
admin_user => 'sa',
|
admin_pass => 'Pupp3t1@',
|
}
|
sqlserver::login{'login1':
|
instance => 'MSSQLSERVER',
|
password => 'Passw0rd!!',
|
login_type => 'SQL_LOGIN',
|
check_expiration => 'true',
|
}
|
2. Login with sa account and make sure login is not been using/login, attempted to delete the login1 by apply the below manifest:
sqlserver::config{ 'MSSQLSERVER':
|
admin_user => 'sa',
|
admin_pass => 'Pupp3t1@',
|
}
|
sqlserver::login{'login1':
|
instance => 'MSSQLSERVER',
|
ensure => 'absent'
|
}
|
Results: login1 is not deleted, applying the manifest throws the errors as below:
PS C:\Users\Administrator> puppet apply C:\cygwin64\tmp\deletelogin1.txt
|
Notice: Compiled catalog for uvmh1uqnfs6qtjh.delivery.puppetlabs.net in environment production in 0.56 seconds
|
Error: BEGIN TRY
|
DECLARE @sql_text as NVARCHAR(max);
|
SET @sql_text = N'USE master
|
GO
|
IF exists(select * from sys.sql_logins where name = ''login1'')
|
BEGIN
|
-- need to add logic to kill all possible connections if any exists,
|
-- possible force flag to prevent from happening during transaction if user would prefer to wait
|
execute sp_droplogin @loginame = [login1]
|
END
|
'
|
EXECUTE sp_executesql @sql_text;
|
END TRY
|
BEGIN CATCH
|
DECLARE @msg as VARCHAR(max);
|
SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
|
THROW 51000, @msg, 10
|
END CATCH
|
returned 1 instead of one of [0]
|
Error: /Stage[main]/Main/Sqlserver::Login[login1]/Sqlserver_tsql[login-MSSQLSERVER-login1]/returns: change from notrun t
|
o 0 failed: BEGIN TRY
|
DECLARE @sql_text as NVARCHAR(max);
|
SET @sql_text = N'USE master
|
GO
|
IF exists(select * from sys.sql_logins where name = ''login1'')
|
BEGIN
|
-- need to add logic to kill all possible connections if any exists,
|
-- possible force flag to prevent from happening during transaction if user would prefer to wait
|
execute sp_droplogin @loginame = [login1]
|
END
|
'
|
EXECUTE sp_executesql @sql_text;
|
END TRY
|
BEGIN CATCH
|
DECLARE @msg as VARCHAR(max);
|
SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
|
THROW 51000, @msg, 10
|
END CATCH
|
returned 1 instead of one of [0]
|
Notice: Applied catalog in 0.10 seconds
|
PS C:\Users\Administrator>
|
3. Modified the manifest to be:
sqlserver::config{ 'MSSQLSERVER':
|
admin_user => 'sa',
|
admin_pass => 'Pupp3t1@',
|
}
|
sqlserver::login{'login1':
|
login => 'login1',
|
instance => 'MSSQLSERVER',
|
ensure => 'absent',
|
}
|
4. Apply the modified manifest in step3, same errors and the login1 is still not deleted
PS C:\Users\Administrator> puppet apply C:\cygwin64\tmp\deletelogin1_again.txt
|
Notice: Compiled catalog for uvmh1uqnfs6qtjh.delivery.puppetlabs.net in environment production in 0.56 seconds
|
Error: BEGIN TRY
|
DECLARE @sql_text as NVARCHAR(max);
|
SET @sql_text = N'USE master
|
GO
|
IF exists(select * from sys.sql_logins where name = ''login1'')
|
BEGIN
|
-- need to add logic to kill all possible connections if any exists,
|
-- possible force flag to prevent from happening during transaction if user would prefer to wait
|
execute sp_droplogin @loginame = [login1]
|
END
|
'
|
EXECUTE sp_executesql @sql_text;
|
END TRY
|
BEGIN CATCH
|
DECLARE @msg as VARCHAR(max);
|
SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
|
THROW 51000, @msg, 10
|
END CATCH
|
returned 1 instead of one of [0]
|
Error: /Stage[main]/Main/Sqlserver::Login[login1]/Sqlserver_tsql[login-MSSQLSERVER-login1]/returns: change from notrun t
|
o 0 failed: BEGIN TRY
|
DECLARE @sql_text as NVARCHAR(max);
|
SET @sql_text = N'USE master
|
GO
|
IF exists(select * from sys.sql_logins where name = ''login1'')
|
BEGIN
|
-- need to add logic to kill all possible connections if any exists,
|
-- possible force flag to prevent from happening during transaction if user would prefer to wait
|
execute sp_droplogin @loginame = [login1]
|
END
|
'
|
EXECUTE sp_executesql @sql_text;
|
END TRY
|
BEGIN CATCH
|
DECLARE @msg as VARCHAR(max);
|
SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
|
THROW 51000, @msg, 10
|
END CATCH
|
returned 1 instead of one of [0]
|
Notice: Applied catalog in 0.10 seconds
|
PS C:\Users\Administrator>
|
- relates to
-
MODULES-3256 sqlserver::login create|delete/login.sql.erb not working with AD logins
-
- Resolved
-