Archive

Uncategorized

Created a new SQL Agent job calling an SSIS package in the local SSIS Catalog.

Error Message:  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Error: 18452, Severity: 14, State: 1.

I struggled with this error knowing that I was working in the same domain and using the same AD account for the Agent and SSIS services.  The connection string in the package was set correctly and worked fine in my individual development environment.

The fix; the server reference in the connection manager was an alias that apparently didn’t have SPN’s correctly created.  I think it was an old alias.  Changed the server reference in the connection manager to the server name as it appears in AD and it worked fine.

 

 

Advertisements

There are many scripts in blogs and forums to remove a user from all databases in a SQL Server instance.  I want to keep as simple as possible and just drop the database user account.  This script will error on the databases where the users owns a schema.  I want to know those databases and want to retain the login.  Plus having heard so many warnings about cursors I don’t use them.  Some would say a loop in a WHILE statement is the same thing as a cursor.

Simply put, get a list of databases to check for the user, loop through each, and drop the user if it exists.

DECLARE @Databasename VARCHAR(128)
, @Command VARCHAR(255)
, @UserName VARCHAR(128)

SELECT @UserName = ” — Enter a User

DECLARE @DBList TABLE
(DatabaseName VARCHAR(128))

INSERT INTO @DBList
( DatabaseName )
SELECT name
FROM master.sys.databases
WHERE name <> ‘master’

WHILE (SELECT COUNT(*) FROM @DBList) > 0
BEGIN
SELECT TOP 1 @Databasename = Databasename
FROM @DBList

SELECT @Command = ‘USE [‘ + @Databasename + ‘] IF EXISTS (SELECT * FROM ‘ + @Databasename + ‘.sys.database_principals WHERE name = N’
+ ”” + @UserName + ”” + ‘) DROP USER [‘+ @UserName +’]’

PRINT @Command
EXECUTE (@Command )

DELETE FROM @DBList WHERE DAtabaseName = @Databasename
END