Quickly Find Failed SQL Server Logins Using the Windows Event Log
Learn how to use custom Event Log views and PowerShell to find failed SQL Server logins.
Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. Brian currently serves as a data architect as well as an independent infrastructure/security architect concentrating on Active Directory, SQL Server, and Windows Server. He has served in a myriad of other positions including senior database administrator, data warehouse architect, web developer, incident response team lead, and project manager. Brian has spoken at 24 Hours of PASS, IT/Dev Connections, SQLConnections, the Techno Security and Forensics Investigation Conference, the IT GRC Forum, SyntaxCon, and at various SQL Saturdays, Code Camps, and user groups.
- MSSQLTips Awards: Author of the Year Contender - 2015, 2017 | Champion (100+ tips) - 2014
Migrate the Correct Logins with a SQL Server Database
Learn how to Migrate the Correct Logins with a SQL Server Database including Windows Users, Windows Groups, SQL Server Logins, mismatched and orphaned logins.
Where Does SQL Server Store Its Certificates
My security team was recently performing security scans and they noticed that when they attempted a connection to my SQL Servers, they were getting self-signed certificates. Where does SQL Server store these certificates? Also, why were they seeing self-s
SQL Server Permissions Granted to All Users By Default
Learn how to determine the SQL Server Permissions Granted to All Users By Default in the public role, in system databases and user defined databases.
Suppressing SQL Server Alerts to Reduce Emails
We have SQL Server alerts configured to notify us when there are issues. However, one problem we have is that sometimes we get a lot of email all at once. How can we suppress SQL Server Alerts to reduce the number of emails?
Scripting SQL Server Database Owners for Disaster Recovery
Learn how to script SQL Server Database Owners for Disaster Recovery in an automated manner with a script, SQLCMD and SQL Server Agent.
Why You Should Avoid SELECT * in SQL Server T-SQL Code
Learn about some of the challenges with having SELECT * in SQL Server T-SQL code including code modifications, source control dependencies and troubleshooting.
Minimum Permissions for SQL Server TRUNCATE TABLE
In this tip we look at different permission levels required to perform a Truncate Table command in a SQL Server database.
Best Practices to Secure the SQL Server sa Account
Learn about the best practices to secure the SQL Server sa account to avoid security issues.
Script SQL Server Logins for Disaster Recovery
In preparation for disaster recovery, I need to make sure I can recover the logins onto another server. In this tip we look at how to setup an automated process to script all SQL Server logins.
How to Clone a SQL Server Login, Part 3 of 3
I have read part 1 and part 2 of your tip series and the tips are great. I have a large environment with numerous SQL Server databases where I need to clone logins. How can I automate this code to save time and ensure accuracy?
How to Clone a SQL Server Login, Part 2 of 3
Learn how to clone a SQL Server login this second part of this series.
How to Clone a SQL Server Login, Part 1 of 3
I need to create a login for my SQL Server and assign it the correct permissions. I already have another login which has the exact permissions I want. Is there a way I can clone the login that has all the permissions?
Recovering a SQL Server TDE Encrypted Database Successfully
I have been tasked with setting up Transparent Data Encryption (TDE) on a database with sensitive information. I need to know the steps to restore this backup to another instance.
Understanding how SQL Server Agent Jobs can share schedules
I have a job that runs at a particular time during the week. However, this week it didn't run when I expected. I haven't modified that job in any way. I did modify another job's schedule, and the job in question ran at the exact time I set the second job
Automated way to get all port information for SQL Server instances
I was asked to catalog all the port information for our SQL Servers and I'm looking to automate things, or at least gather the information without logging on to each server. In this tip we look at how this can be done.
Finding Encrypted Data in a SQL Server Database
I've recently taken over a SQL Server and I have been asked if there is encrypted data in any of the user databases. In this tip we look at how to figure this out.
Catalog SQL Server Services Installed on Servers
I want to get a catalog of all the SQL Server services running on my servers. How might I do this? I don't want to take a bunch of screenshots of SQL Server Configuration Manager or SQL Server Management Studio. Surely there's a better way!
Configuring Critical SQL Server Alerts
In this article, we look at how to setup alerts 17 through 25 using SQL Server Agent Alerts to alert you when an issue occurs.
What Windows Server Groups Should I Audit on my SQL Servers?
I'm responsible for the security of my organization's SQL Servers. Lately I've been looking outside of SQL Server and wondering what I should be checking. Someone mentioned the Administrators group and that made me think about other groups on the server.
Auditing Critical Windows Files and Folders for SQL Server
In our extract, transform, and load (ETL) processing, we have some sensitive files and folders. Files containing financial information, as well as configuration files and the like, are stored on disk. We need to know if someone touches them. Is there a wa
Add a Salt with the SQL Server HASHBYTES Function
Learn how to store password hashes in SQL Server using the HASHBYTES function as well as a salt to encrypt the password.
How to Audit for SQL Server Users in Contained Databases
I know SQL Server 2012 added a feature where you can use contained databases. I also have heard that these databases can have users which can be authenticated by the SQL Server. However, the users don't appear in the standard sys.sql_logins or sys.server_
How come I can create SQL Server objects but not access these objects
In a database I was able to create a stored procedure. However, when I went to execute the stored procedure, SQL Server told me I didn't have permission to do so. Is this a bug or did I do something wrong? In this tip we look at this issue.