Unable to Drop a User from SSISDB?

I ran into an issue when trying to drop a user from SSISDB (the Integration Services Catalog database). 

The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.

The transaction ended in the trigger. The batch has been aborted.

How to solve it?

Find where the user has permissions

USE SSISDB

go

SELECT *

FROM catalog.object_permissions

WHERE grantee_sid = SUSER_SID('YourUserName');

--change YourUserName to your actual username

Revoke permissions

REVOKE READ ON OBJECT::[folder_name] TO [YourUserName];

REVOKE MODIFY ON OBJECT::[project_name] TO [YourUserName];

Drop the user

DROP USER [YourUserName];

SQL Server Config Manager Error "MMC could not create the snap-in

 Hi, I have seen this error elsewhere online. I have gone to mmc to enable the snap in and I still have had no fix. My computer is running Windows Server 2022, SQL Server Express 2022, and SSMS. I have reinstalled, repaired, and all of the other tricks. Help!

Solution: Re-register the Configuration Manager Snap-In Manually

-->Open Command Prompt as Administrator

-->Run the following command:(For SQL Server 2022, the version folder is 160. Adjust the path if yours is different.)

-->cmdmofcomp "C:\Program Files (x86)\Microsoft SQL Server\160\Shared\sqlmgmproviderxpsp2up.mof"

-->Restart your machine

How to Fix: 'Microsoft.ACE.OLEDB.12.0' Provider is Not Registered on the Local Machine

 If you encounter the error:

'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

while importing Excel or CSV files into SQL Server, don't worry! This is a common issue caused by a missing or mismatched version of the Microsoft Access Database Engine.

Download the Microsoft Access Database Engine 2016 Redistributable

1. Go to the official Microsoft download page:
   Microsoft Access Database Engine 2016 Redistributable

2. Select the version based on your system:

• 32-bit version (recommended even on 64-bit systems if you're using 32-bit SQL Server tools).
• 64-bit version (only if you're sure all tools are 64-bit).

Note: Using the 32-bit version resolves compatibility issues with tools like SQL Server Management Studio (SSMS), which often run in 32-bit mode.

Install Using Command Prompt (Silent Install)

Once downloaded, follow these steps to install properly:

1. Open Command Prompt as Administrator.

2. Navigate to the folder where the .exe file is downloaded.

  Example Command:  cd C:\Test\AccessDatabaseEngine.exe /quiet

If your file is named differently (like AccessDatabaseEngine_X64.exe), use the exact name.

This will install the provider quietly without UI interruptions.

Restart Your Tools

After installation:

• Restart SQL Server Management Studio (SSMS).

• Try your import or export task again.

You should no longer see the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine error!

T-SQL SQL Server Backup Report: Last Full, Differential, and Log Backup Details

Monitoring your SQL Server backups is crucial for disaster recovery planning. 

Below is a powerful SQL query that retrieves the last full, differential, and log backup details for each database.

SELECT 

    db.name AS DatabaseName,

    -- Last Full Backup Details

    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS LastFullBackupTime,

    MAX(CASE WHEN bs.type = 'D' THEN bmf.physical_device_name END) AS FullBackupLocation,

    -- Last Differential Backup Details

    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS LastDifferentialBackupTime,

    MAX(CASE WHEN bs.type = 'I' THEN bmf.physical_device_name END) AS DifferentialBackupLocation,

    -- Last Log Backup Details

    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS LastLogBackupTime,

    MAX(CASE WHEN bs.type = 'L' THEN bmf.physical_device_name END) AS LogBackupLocation,

    -- Backup Status

    CASE 

        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL THEN 'No Full Backup Found'

        ELSE 'Full Backup Available'

    END AS FullBackupStatus, 

    CASE 

        WHEN MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) IS NULL THEN 'No Differential Backup Found'

        ELSE 'Differential Backup Available'

    END AS DifferentialBackupStatus,

    CASE 

        WHEN MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL THEN 'No Log Backup Found'

        ELSE 'Log Backup Available'

    END AS LogBackupStatus

FROM msdb.dbo.backupset bs

JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id

JOIN sys.databases db ON bs.database_name = db.name

GROUP BY db.name

ORDER BY db.name;

SQL Server T-SQL Queries for Employee Data Management

Managing employee data efficiently requires a strong understanding of SQL queries. Below are 32 essential SQL Server queries that cover common use cases, such as fetching, filtering, updating, and analyzing employee records.

-- 01. Fetch all employees whose salary is greater than 50,000.

SELECT * FROM Employees WHERE Salary > 50000;

-- 02. Fetch all employees working in the "IT" department.

SELECT * FROM Employees WHERE Department = 'IT';

-- 03. Fetch employee names and their departments.

SELECT Name, Department FROM Employees;

-- 04. Fetch the top 3 highest-paid employees.

SELECT TOP 3 * FROM Employees ORDER BY Salary DESC;

-- 05. Fetch employees whose names start with the letter "A".

SELECT * FROM Employees WHERE Name LIKE 'A%';

-- 06. Fetch employees who were hired in the year 2022.

SELECT * FROM Employees WHERE YEAR(HireDate) = 2022;

-- 07. Count the total number of employees in each department.

SELECT Department, COUNT(*) AS TotalEmployees FROM Employees GROUP BY Department;

-- 08. Find the average salary of employees in the "Finance" department.

SELECT AVG(Salary) AS AvgSalary FROM Employees WHERE Department = 'Finance';

-- 09. Fetch employees with salaries between 30,000 and 60,000.

SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 60000;

-- 10. Fetch all employees who do not belong to the "HR" department.

SELECT * FROM Employees WHERE Department <> 'HR';

-- 11. Fetch the details of employees who have not been assigned a department (NULL department).

SELECT * FROM Employees WHERE Department IS NULL;

-- 12. Fetch employee details sorted by their salaries in descending order.

SELECT * FROM Employees ORDER BY Salary DESC;

-- 13. Fetch duplicate employee names from the employee table.

SELECT Name, COUNT(*) FROM Employees GROUP BY Name HAVING COUNT(*) > 1;

-- 14. Fetch the department name and the highest salary in each department.

SELECT Department, MAX(Salary) AS HighestSalary FROM Employees GROUP BY Department;

-- 15. Update the salary of employees in the "IT" department by 10%.

UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';

-- 16. Delete employees whose salaries are below 20,000.

DELETE FROM Employees WHERE Salary < 20000;

-- 17. Insert a new employee into the table.

INSERT INTO Employees (Name, Department, Salary, HireDate, Email, ManagerID) 

VALUES ('New Employee', 'IT', 55000, '2024-01-31', 'new.employee@company.com', NULL);

-- 18. Fetch employees whose names contain the substring "Ajay".

SELECT * FROM Employees WHERE Name LIKE '%Ajay%';

-- 19. Fetch employees whose email IDs end with "@sqldbanow.com".

SELECT * FROM Employees WHERE Email LIKE '%@sqldbanow.com';

-- 20. Find the total salary paid to employees in each department.

SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department;

-- 21. Fetch the employee with the second-highest salary.

SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees));

-- 22. Fetch the number of employees hired in each year.

SELECT YEAR(HireDate) AS Year, COUNT(*) AS TotalEmployees FROM Employees GROUP BY YEAR(HireDate);

-- 23. Fetch employees who share the same salary as another employee.

SELECT * FROM Employees WHERE Salary IN (SELECT Salary FROM Employees GROUP BY Salary HAVING COUNT(*) > 1);

-- 24. Fetch employees along with their manager's name (using self-join).

SELECT e1.Name AS Employee, e2.Name AS Manager FROM Employees e1 LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

-- 25. Fetch employees with the same department and job title as "John Doe."

SELECT * FROM Employees WHERE Department = (SELECT Department FROM Employees WHERE Name = 'John Doe') 

AND JobTitle = (SELECT JobTitle FROM Employees WHERE Name = 'John Doe');

-- 26. Find the maximum and minimum salary of employees.

SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;

-- 27. Fetch employees who were hired in the last 6 months.

SELECT * FROM Employees WHERE HireDate >= DATEADD(MONTH, -6, GETDATE());

-- 28. Fetch employees who have worked for more than 5 years.

SELECT * FROM Employees WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;

-- 29. Fetch all employees who do not have a manager assigned.

SELECT * FROM Employees WHERE ManagerID IS NULL;

-- 30. Fetch the first name, last name, and full name of employees.

SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

-- 31. Fetch employees grouped by department and sorted by the total salary in descending order.

SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department ORDER BY TotalSalary DESC;

-- 32. Fetch all employees whose salaries are more than the average salary.

SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);