A very useful tool in SQL Server is the database email service. For our latest project we needed to enable email updates on the recording of new data in our system. These instructions require the SQL Database Email service to already be configured, if you need instructions on how to do this please see my… Continue reading Create a SQL Server Database Email trigger, when a tables records are updated.
Tag: SQL
Setting up SQL Server database email
To enable the database email service in SQL Server, you need to take a few steps to configure in your database. The benefits of enabling this service are you can automate emails for query results, job success or failures or even trigger emails based on table changes in your system, great for notifications. First of… Continue reading Setting up SQL Server database email
SQL Server query to get all stored procedures, tables and views execute permissions
To list all Stored Procedures in your database that an account has execute permissions on, simply update the "Execute as user" statement below.. EXECUTE AS user = 'myUsername' SELECT SUSER_NAME(), USER_NAME(); select name, has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute from sys.procedures revert; To list all permissions applied explicitly to tables and views for a username, use… Continue reading SQL Server query to get all stored procedures, tables and views execute permissions
SQL Server query to list all server agent jobs, owners and reassign ownership
If you are auditing security in your system a useful query can list all of your SQL Server Agent Jobs in MS SQL with the account ownership. SELECT s.name AS JobName, l.name AS JobOwner FROM msdb..sysjobs s LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid WHERE l.name IS NOT NULL ORDER by l.name Or with… Continue reading SQL Server query to list all server agent jobs, owners and reassign ownership
Identifying unused or inefficient indexes in SQL
SQL Server, like other relational databases, utilises indexing as a way to improve query performance. In a nutshell indexing is used to find data fast. However, it's important to remember that indexing comes at a cost, and as a database administrator, or developer, understanding how indexing actually works is fundamental to maintaining a performant system.… Continue reading Identifying unused or inefficient indexes in SQL
Search SQL Stored Procedures for text
On occasion I have found it useful to search for stored procedures based on containing text. It doesn't happen often as your naming convention should be enough to explain the purpose of your procedure, and be easy to find. However, in the real world, when looking at legacy systems it may be of use. Another… Continue reading Search SQL Stored Procedures for text
Phil's learning log 