You may see a Windows user accessing SQL Server but they are not showing as a login.
The following command should assist with showing which security group(s) gives them access.
EXEC xp_logininfo 'yourdomain\usersname','all';
You may see a Windows user accessing SQL Server but they are not showing as a login.
The following command should assist with showing which security group(s) gives them access.
EXEC xp_logininfo 'yourdomain\usersname','all';
A database owner is typically set to ‘sa’ to mitigate having orphaned owners when the person who created the database leaves the business.
The legacy way of changing a database owner was:
USE [databasehavingnamechanged]
GO
EXEC sp_changedbowner 'sa';
GO
The recommended SQL Server 2008 and newer way is:
USE [master]
GO
ALTER AUTHORIZATION ON DATABASE::[databasehavingnamechanged] TO [sa];
GO
The following alerts are typically setup at a minimum to keep an eye on your SQL Server.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sql_alerts',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'destinationemailaddress1@yourcompany.com;destinationemailaddress2@yourcompany.com',
@category_name=N'[Uncategorized]'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_14',
@message_id=0,
@severity=14,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_14', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_15',
@message_id=0,
@severity=15,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_15', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_16',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_16', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_17',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_17', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_18',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_18', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_19',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_19', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_20',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_20', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_21',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_21', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_22',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_22', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_23',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_23', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_24',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_24', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_25',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_25', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_823', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_824', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_825', @operator_name=N'sql_alerts', @notification_method = 1;
GO
It’s quite common for an Identity column to be configured as an INT data type and the business is impacted when it fills-up.
Ideally the column would be changed to the BIGINT data type and the issue is resolved; however, sometimes this change is not quickly possible due to other objects using the column and/or resource constraints i.e. indexes, available disk space etc., and you have to stick with INT and transition to BIGINT at a later stage when possible.
If you need to stay with INT, the common solution is to start the reseed at -2,147,483,648 and let it work back to 0.
A test example for you is shown below:
/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (1,1), c2 float);
/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view records */
SELECT * FROM test_ident;
/* reseed from -2,147,483,648 working back to 0*/
DBCC CHECKIDENT ('test_ident', RESEED, -2147483648);
/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view records */
SELECT * FROM test_ident;
/* drop test table */
DROP TABLE test_ident;
Below is an example of changing the Identity column to the BIGINT data type, if suitable for your scenario.
NOTE: A schema modify (SCH-M) lock is required on the table while the ALTER command runs.
/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (2147483647,1), c2 float);
/* try insert some records and only 1 will succeed*/
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view record */
SELECT * FROM test_ident
/* change indentity column to bigint */
ALTER TABLE test_ident ALTER COLUMN c1 bigint;
/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view records */
SELECT * FROM test_ident
/* drop test table */
DROP TABLE test_ident;
NOTE: If you use SSMS to change the column type, it will drop and re-create the table. Therefore, rather use the ALTER TABLE command.
Another consideration is the impact that the reseed will have on your sort order.
Below demonstrates the effect of using a negative increasing reseed vs a negative decreasing reseed and if you need to emulate the BIGINT data type i.e. show values larger than 2,147,483,647.
/* reseed starting from -2147483648 and increasing */
DECLARE @t1 TABLE (ident int, inserted int);
INSERT INTO @t1 (ident,inserted) VALUES (1,1);
INSERT INTO @t1 (ident,inserted) VALUES (2,2);
INSERT INTO @t1 (ident,inserted) VALUES (3,3);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483648,4);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483647,5);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483646,6);
SELECT
ident
,inserted AS reseed_increasing
,CASE WHEN ident < 0 THEN ((2147483648 * 2) + CAST(ident AS bigint)) ELSE ident END AS emulate_bigint
FROM @t1 ORDER BY ident ASC;
/* reseed starting from -1 and decreasing */
DECLARE @t2 TABLE (ident int, inserted int);
INSERT INTO @t2 (ident,inserted) VALUES (1,1);
INSERT INTO @t2 (ident,inserted) VALUES (2,2);
INSERT INTO @t2 (ident,inserted) VALUES (3,3);
INSERT INTO @t2 (ident,inserted) VALUES (-1,4);
INSERT INTO @t2 (ident,inserted) VALUES (-2,5);
INSERT INTO @t2 (ident,inserted) VALUES (-3,6);
SELECT
ident
,inserted AS reseed_decreasing
,CASE WHEN ident < 0 THEN 2147483647 - CAST(ident AS bigint) ELSE ident END AS emulate_bigint
FROM @t2 ORDER BY ident ASC;
Below attempts to help with understanding deadlocks:
Objective
Rules
Player 1 | Player 2 | |
Time (HH:MM:SS) | Objective: Obtain Book A and then Book B. | Objective: Obtain Book B and then Book A. |
10:00:00 | Enters the room. | |
10:00:01 | Enters the room. | |
10:00:05 | Finds Book A and picks it up. | |
10:00:15 | Finds Book B and picks it up. | |
10:01:01 | 1 minute has passed. Sees that Book B is held by Player 2 and waits. | |
10:01:15 | 1 minute has passed. See that Player 1 has Book A. As per the rules; Player 2 acknowledges that they cannot obtain Book A without waiting and as Player 1 had entered the room first, Player 2 drops their book and exits the room (Player 2 is the DEADLOCK VICTIM). | |
10:01:16 | Picks it up Book B. | |
10:01:17 | Enters the room. | |
10:01:18 | Sees that Book B is held by Player 1 and waits. | |
10:02:16 | 1 minute has passed and the objective have been achieved. Drops both books and exits the room. | |
10:02:17 | Sees that Book B is available and picks it up. | |
10:03:17 | 1 minute has passed and picks up Book A. | |
10:04:17 | 1 minute has passed and the objective has been achieved. Drops both books and exits the room. |
If we now think of this in terms of SQL Server, a typical deadlock occurs as follows:
Session 1 | Session 2 | |
Time (HH:MM:SS.nnn) | ||
10:00:00.000 | Begins Transaction 1. | |
10:00:00.001 | Begins Transaction 2. | |
10:00:00.002 | Starts a statement updating Table A. | |
10:00:00.003 | Starts a statement updating Table B. | |
10:00:00.005 | Finished updating Table A and waits to update Table B. | |
10:00:00.007 | Finished updating Table B. Needs to update Table A but cannot as it is still held by the Session 1 Transaction 1. Deadlocks, rolls back its changes and exits Transaction 2. | |
10:00:00.008 | Starts a statement updating Table B. | |
10:00:00.009 | Session reconnects and begins Transaction 3. Sees that Table B is held by Session 1 and waits. | |
10:00:00.101 | Finished updating Table B and ends Transaction 1. | |
10:00:00.102 | Starts a statement updating Table B. | |
10:00.00.106 | Finished updating Table B. Starts a statement updating Table A. | |
10:00.00.108 | Finished updating Table A and ends Transaction 3. |
Essentially:
What you can try do to help reduce Deadlocks:
Below is a list of Skype for Business databases that you may come across.
cpsdyn |
lis |
rgsconfig |
rgsdyn |
rtcab |
rtcshared |
rtcxds |
xds |
When working with XML text the following common conversions are good to know.
XML | Character |
& | & |
< | < |
> | > |
" | “ |
' | ‘ |

 | Carriage Return (CR) |

 | Line Feed (LF) |
To cast a text column which contains XML to human friendly text you could use the REPLACE function e.g.
DECLARE @xml_text nvarchar(MAX);
SET @xml_text = '& < > " ' 
';
SELECT @xml_text AS xml_text;
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@xml_text
, '&' , '&')
, '<' , '<')
, '>' , '>')
, '"', '"')
, ''', '''')
, '
' , ' ')
, '
' , ' ')
AS converted_text;
A reverse example of the above e.g. xml to nvarchar, would be:
DECLARE @xml xml;
SET @xml = '<test/> ';
SELECT @xml AS [xml];
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(@xml AS nvarchar(MAX))
, '&', '&')
, '<', '<')
, '>', '>')
, '"', '"')
, '''', ''')
, ' ', '
')
, ' ', '
')
AS converted_xml;
Which Windows Defender Firewall profile causes the Windows Firewall rule warning?
It is the Public Profile.
If this profile is disabled, the warning does not show.
So the next question; when should the Windows firewall be disabled?
Answer: Never; unless you have other another firewall solution. Rather add an appropriate access rule.
When installing SQL Server, why do you sometimes see that the Database Engine defaults to the SQL_Latin1_General_CP1_CI_AS collation and other times another value?
This value is determined by the system locale which can be found via Control Panel -> Clock and Region -> Region -> Administrative -> Language for non-Unicode programs.
Here in New Zealand we typically use the Latin1_General_CI_AS collation.
When the system locale is changed to “English (New Zealand)” a prompt to restart Windows is received.
After a restart, the result of this change means that the SQL Server installation will now default to Latin1_General_CI_AS.
© 2011-2022 AKAWN®. All rights reserved. Privacy Policy.