In SQL Server administration, there are two terms that can easily be confused: SQL Login and SQL User. While SQL Login allows you to manage permissions for server access, SQL User defines permissions for database access. In this article, we’ll take a closer look at SQL Login. SQL Login It provides access to SQL Server.… Read More »
Reporting services are a feature that you can use starting from the Express edition, and it is a set of services that allow you to create and publish reports. I have written a series of articles about Reporting services (the first part is here). Users can view these reports on the URL within the internal… Read More »
In the previous two articles, I covered setting up database mail (gmail) and configuring Change Data Capture (CDC), which is the automatic tracking of changes to a table. So, it occurred to me, why not combine these topics and describe how to use automatic email to report changes to a specific table? This could come… Read More »
DBCC stands for database console commands, which are commands executed on a specific database. The DBCC command set allows you to perform various administrative operations or queries on databases through scripting, instead of using the graphical interface of Management Studio. To execute these commands, you typically need a high-level role (such as sysadmin, serveradmin, db_owner,… Read More »
SQL Server offers 3 types of database recovery models: simple recovery model, full recovery model, and bulk-logged recovery model. The recovery model defines how SQL Server manages transactions and transaction logs and whether it stores or doesn’t store these data. Ultimately, this affects the options available for restoring data from backups. Each database can have… Read More »
A table without a primary key (clustered index) is referred to as a heap because it’s just a pile of unordered data. In contrast, a table with a clustered index is better structured into a balanced tree. The primary key itself is a clustered index and every table should have one. An optimized table or… Read More »
The Database Mail feature is only available in the SQL Server Standard edition and higher. In the SQL Server Express edition, unfortunately, it is not available. This feature is useful primarily if you want to send various messages from SQL Server to your email. Typically, this involves various checks, notifications, and alerts. Finally, this feature… Read More »
In applications or complex enterprise information architectures, there is often a need to handle historical records and track changes in values for various reasons. Most of the time, it’s for security purposes; we need to monitor who is making changes and be able to correct errors, whether they are from users or database administrators. Another… Read More »
In SQL Server, there is a system table that stores all possible error states and their severity levels. Error severity ranges from 0 to 25 and defines the seriousness of an error. All sql server error messages that you may encounter are stored in the sys.messages table. USE MASTER SELECT * FROM SYS.MESSAGES WHERE language_id… Read More »
xp_cmdshell is a very powerful procedure that allows you to execute commands via the command-line (cmd) in SQL Server. With cmdshell, you can perform various operations on a Windows server through the SQL platform, such as copying files, creating, deleting, running bcp for data import and export, and more. However, there are significant security risks… Read More »