Tag: SQL server

1. In our example, we’ll assign the SQL Security Manager role to the user UserSqlSecurityManager@contoso.onmicrosoft.com. Using privileged user that can assign Microsoft Entra roles, sign into the Azure portal.
2. Go to your SQL server resource, and select Access control (IAM) in the menu. Select the Add button and then Add role assignment in the drop-down menu.
3. In the Add role assignment pane, select the Role SQL Security Manager, and select the user that you want to have the ability to enable or disable Microsoft Entra-only authentication.
4. Click Save

Enable Microsoft Entra-only authentication;

Enable in SQL Database using Azure portal

To enable Microsoft Entra-only authentication in the Azure portal, follow these steps:

1. Using the user with the SQL Security Manager role, go to the Azure portal.
2. Go to your SQL server resource, and select Microsoft Entra ID under the Settings menu.
3. If you haven’t added an Microsoft Entra admin, you’ll need to set this before you can enable Microsoft Entra-only authentication.
4. Check the box for Support only Microsoft Entra authentication for this server.
5. The Enable Microsoft Entra-only authentication popup will show. Select Yes to enable the feature and Save the setting.

Enable in SQL Managed Instance using Azure portal

To enable Microsoft Entra-only authentication in the Azure portal, see the steps below.

1. Using the user with the SQL Security Manager role, go to the Azure portal.
2. Go to your SQL managed instance resource, and select Microsoft Entra admin under the Settings menu.
3. If you haven’t added an Microsoft Entra admin, you’ll need to set this before you can enable Microsoft Entra-only authentication.
4. Select the Support only Microsoft Entra authentication for this managed instance checkbox.
5. The Enable Microsoft Entra-only authentication popup will show. Select Yes to enable the feature and Save the setting.

Reference

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication-tutorial?view=azuresql&tabs=azure-portal

Permissions

Requires ALTER ANY LOGIN permission.

If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

• Resetting the password without supplying the old password.
• Changing the login name.
• Enabling or disabling the login.
• Mapping the login to a different credential.

A principal can change the password for its own login.

Reference

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql?view=azuresqldb-current&preserve-view=true#b-changing-the-password-of-a-login-1

We starting to get a lot of stored procedures in our application. Many of them are for custom reports many of which are no longer used. Here is a query that can be run on the system views in SQL Server 2005 – 2022 that would tell us the last date a stored procedure was executed?

SELECT o.name, 
       ps.last_execution_time 
FROM   sys.dm_exec_procedure_stats ps 
INNER JOIN 
       sys.objects o 
       ON ps.object_id = o.object_id 
WHERE  DB_NAME(ps.database_id) = '' 
ORDER  BY 
       ps.last_execution_time DESC  

Reference

https://stackoverflow.com/questions/595742/last-run-date-on-a-stored-procedure-in-sql-server

The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHAR column can be. However, if you do have a lot of data you will have a huge performance issue using these methods.

In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.

If you are searching large amounts of text and performance is key then you should use a Full Text Index.

LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.

Reference

https://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server

I would like to find all the stored procedures that has reference of Functions or called Functions from Store procedures.

Here is the script;

Select 
	Schema_Name(p.schema_id) As ProcedureSchema, p.name As ProcedureName, 
	Schema_Name(f.schema_id) As FunctionSchema, f.name as FunctionName
From sys.sql_expression_dependencies d
Inner Join sys.objects p On 
	p.object_id = d.referencing_id And p.type_desc = 'SQL_STORED_PROCEDURE'
Inner Join sys.objects f On 
	f.object_id = d.referenced_id And f.type In ('AF', 'FN', 'FS', 'IF', 'TF');