Create the user in the master database

--Use Master Database
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER
ALTER ROLE dbmanager ADD MEMBER [user@domain.com] ;
ALTER ROLE loginmanager ADD MEMBER [user@domain.com];
Grant Alter any user to [user@domain.com] with GRANT OPTION

Now select the user database from the db drop down menu in SQL Server Management Studio

--Use UserDatabase
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [user@domain.com]; --this grants the db permission to the user
Grant Alter any user to [user@domain.com] with GRANT OPTION  --this grants the user permission to modify permissions for other users.
EXEC sp_addrolemember '[schema_name]','user@domain.com' --this adds the user to a specify schema role

Hope this helps you to grant users permission to Azure Synapse Databases.

Database Mail Troubleshooting

I use the following scritps to troubleshoot database mail issues.

–Get a list of email sent items
SELECT * FROM dbo.sysmail_sentitems

–Another version of the script to get a list of email sent items
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
order by send_request_date desc

–Check to see if broker service is enabled
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’;

–Check if database mail service is running
EXECUTE msdb.dbo.sysmail_help_status_sp

For SQL 2016 – you will need to have .net 3.5 installed on the server for db mail to work

EXEC msdb.dbo.sysmail_help_configure_sp;

–Get the name of the database profile and account name
EXEC msdb.dbo.sysmail_help_account_sp; –this will show the db mail account name
EXEC msdb.dbo.sysmail_help_profile_sp; –this will show the db mail profile name
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

I received a request to export data from a table in a database in Production to a similar table in a database in the Development environment. I used the export/import wizard thru SQL Server Management Studio but my export was failing giving me an error that the data cannot be copied because Foreign Key was present in the destination database. In the past I would just script out a drop and create script for all the foreign keys then drop all the Foreign Keys, do the data export and then re-create the Foreign Keys. After some research online I came across a better option to just disable the Foreign Keys instead of dropping and recreating them.

But first lets understand what is a Primary Key and Foreign Key.

In SQL Server, a primary key is a single field that has a unique value to define a record. Fields that are part of the primary key cannot contain a null value. A table can have only one primary key. Usually the primary key is used as an index but this can vary.

A table can have only ONE primary key and this primary key can consist of single or multiple columns (fields).

Since primary key constraints ensure unique data, they are often called identity columns.

When you designate a primary key constraint for a table, the SQL engine enforces data uniqueness by auto create a unique index for the primary key columns.

A foreign key is a column or set of columns that allows developers to establish a referential link between the data in two different tables. This link helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.

A FOREIGN KEY is a field (or collection of fields) in one table, that links to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced table (parent table).

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column in the child table, because it has to be one of the values contained in the parent table.

Based on the developer’s coding standard – usually it is a good practice to prefix with FK_{FK name} and the same goes with Primary Keys being prefixed with PK_{PK Name}

The following SQL query creates a FOREIGN KEY on the “PersonID” column in the Persons table when the “Orders” table is created:

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

If the Orders table is already created then use this SQL query to create a FOREIGN KEY constraint on the “PersonID” column:

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

If you need to name a Foreign Key constraint and to specify a Foreign Key constraint on multiple columns, use this SQL Query:

ALTER TABLE Orders

ADD CONSTRAINT FK_PersonOrder

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

You can disable a Foreign Key in a table using the Alter Table statement in SQL Server Management Studio. Here is the syntax to disable a foreign key in SQL Server (T-SQL):

ALTER TABLE [your_table_name]

NOCHECK CONSTRAINT [your_fk_name];

Parameters/Syntax:

your_table_name

The name of the table where the foreign key has been created.

your_fk_name

The name of the foreign key that you wish to disable.

The above script would use the ALTER TABLE statement to disable the constraint called fk_inyour_fk_name on the your_table_name table.

After you have disabled the Foreign Key then you should be able to do your data load using the Daa with no error.

To disable all constraints

— disable all constraints

EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

To turn the constraints back on – the print command is optional and it is just for listing the database tables.

Run this:

— enable all constraints

exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

To disable the constraints is much helpful when you have to copy data from one database to another. I prefer this then dropping constraints.

If you have triggers in the database then you will have to disable the triggers prior to your data load and then add the triggers back on once the data load is completed.

To disable all constraints and triggers run this:

sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all constraints and triggers run this:

exec sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”

The word of caution is disabling constraints and triggers – to you have make sure there are no new deltas being written to the database by the users because once you disable all the constraints and triggers any new deltas written to the database that might violate the integrity of the database. Hence you have to ensure that all application traffic is stopped.

Also if you need to import a large amount of data, then consider using BULK INSERT because this method does not fire the triggers. However after your bulk insert it completed, you will need to fix any data integrity issues which occurred during your bulk insert that circumvented the trigger policies.

Hope this helps clarify the concept of Primary Keys, Foreign Keys and Constraints.

There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it is helpful to grant this permission to non-dba personnel if they need to do any performance troubleshooting.

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose sql server problems, and tune databae performance.

There are two different types of DMVs and functions:

Server-scoped DMVs and functions – These require VIEW SERVER STATE permission on the SQL Server level.
Database-scoped DMVs and functions – These require VIEW DATABASE STATE permission on each of the database.

VIEW SERVER STATE is a server level permission that grants non-sysadmin users the ability to view results of Dynamic Management Views.

Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a given database instance at a given time. Since DMVs were introduces in SQL Server 2005 and with each new release of SQL Server, Microsoft has been adding additional DMVs (Dynamic Management Views) to help troubleshoot the performance of the SQL Servers. These objects are helpful to monitor the database server in an efficient and controlled manner.

DMVs come in two subsets – Dynamic Management Views (DMVs) and DMFs (Dynamic Management Functions) and are classified as Dynamic Management Objects (DMOs).
DMVs are like any other views where you can select data from them. DMVs require values to be passed to the function just like any other functions. I will go into the details of DMFs in a separate blog.

If the user does not have sysadmin priveleges or has been granted VIEW SERVER STATE permission and tries to run the following as an example:

SELECT * FROM sys.dm_os_wait_stats

The user will see an error like this one:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

We run the following query to grant the user the access to the entire SQL Server Instance:

GRANT VIEW SERVER STATE TO dbuser1

This needs to be run on the master database.
Once the above script is executed successfully then that specific user will be able to view Dynamic Management Objects to do any type of performance troubleshooting.

In the case of a individual database scoped access, a user might have data reader access to the database but when the users runs this query:

SELECT * FROM sys.dm_db_partition_stats

They get an error, because this query is trying to access a DMV in the specific database.

WE need to grant VIEW DATABASE STATE for this.

We run the following query to grant the user the access to a specific database:
grant VIEW DATABASE STATE to []

USE [DatabaseName]
grant VIEW DATABASE STATE to [<DBUSer>]

Once you run the above script then the user will be able to get a result from:

SELECT * FROM sys.dm_db_partition_stats

Now lets talk about the security risks for granting VIEW SERVER STATE permission.
Yes, there is a risk in providing VIEW SERVER STATE to non-dba and non-sysadmin personnel since they aren’t supposed to see this level of intricate database information on the sql instance.
As an example: sys.dm_exec_connections, sys.dm_exec_cached_plans, sys.dm_exec_requests, sys.dm_exec_query_stats, all provide information about execution contexts and plans, and when the user uses these with sys.dm_exec_sql_text() or sys.dm_exec_query_plan(), it will give this user information about the sql code and objects in the databases. This is usually a security violation in some organizations.

The VIEW SERVER STATE permission gives the database user unrestricted access to this information when using these DMVs. Althought they cannot change anything in the database, but they can get detailed database execution info that may not be allowed to view based on the user’s organizational responsibilites. I would analyze this type of access request with a grain of salt and ask some questions as to the security implications of this user requesting access having access to the database execution info. Also the user will be able to use DMV’s to look at queries. If the queries or some query parameters can contain confidential information that the user wouldn’t otherwise be able to see prior to granting this access – allowing VIEW SERVER STATE would allow them to do so (dateofbirth or socialscecurity #s)

Hope this helps clarify the differences between VIEW SERVER STATE and VIEW DATABASE STATE permission grants.