To create a new user for BYOD (Azure SQL database)

Sometimes, when working with Dynamics 365 for Finance and Operations, we go with the option of BYOD (Bring your own database) in order to enable Asynchronous integrations. In such one scenario, I had to create a new db user for the BYOD Azure SQL database and below are the scripts for the same

-- create SQL auth login from master 

CREATE LOGIN test WITH PASSWORD = 'SuperSecret!' 

-- select your db in the dropdown and create a user mapped to a login 

CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA = dbo; 

-- add user to role(s) in db 

ALTER ROLE db_datareader ADD MEMBER [test]; 

ALTER ROLE db_datawriter ADD MEMBER [test]; 

These are the three steps to be performed in order to add new login / user for your BYOD. 

More info in this blog.

SQL scripts repo

Just sharing a collection of SQL commands which we can use for various information retrieving w.r.t databases and tables in an SQL instance. Will try to keep this post updated with the commands I encounter. 

Go here... https://github.com/dax516/SQL/    

• One example is as below is a command to check the number of tables in a particular database
  USE [YOUR DATABASE NAME]
  SELECT COUNT(*) from information_schema.tables
  WHERE table_type = 'base table' 

Happy coding! 

Rebuild index for all tables in a SQL Express DB

I have this weird situation in one of my ongoing projects, where there are several hundreds of SQL Express instances hosting Dynamics AX Channel Databases which are spread across few high performance virtual servers. Over a time we noticed that Server performance has reduced and a result of a good meeting with all the parties involved - we identified that SQL index rebuild was never performed even with loads of transactions and master data has been moving via the Channel databases. 

The obvious reason being - the SQL instances are Express and it doesn't support SQL Agent - to simply configure a Rebuild job out of the box. The below script can be helpful in order: 

1. Get the current status of Index update
2. Update indexes for all objects in all databases in a particular SQL instance. 

https://github.com/dax516/SQL/blob/master/DatabaseLevelIndexRebuild.sql

Hope this helps. Happy coding!