SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Stairway to Advanced T-SQL Level 8: Functions to Generate Date and Time values

Greg Larsen from SQLServerCentral

When you build applications that store records in SQL Server you will most likely have to store date and time values as part of the data. To manage all the different date related tasks you might need to perform Microsoft has introduced a number of date functions. In this stairway I will be exploring those date and time functions.

How to Provision a Set of Databases to Multiple Azure-based Servers

Additional Articles from Redgate

Grant Fritchey shows how to provision a group of interdependent databases, masked to protect sensitive or personal data, to each machine in an Azure-based test cell.

The 2019 State of SQL Server Monitoring Report

Additional Articles from Redgate

Redgate's second annual State of SQL Server Monitoring survey provides new insights into how the world of SQL Server monitoring is adjusting and adapting to emerging challenges, such as compliance regulations and cloud technologies, while keeping on-top of the usual issues such as deployments, availability, and capacity.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Redgate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".

From the SQL Server Central Blogs - Default resource limits for Windows vs Linux containers in Docker Desktop

dbafromthecold@gmail.com from The DBA Who Came In From The Cold

Docker Desktop is a great product imho. The ability to run Windows and Linux containers locally is great for development and has allowed me to really dig into SQL...

From the SQL Server Central Blogs - Trailing Spaces in SQL Server

Bert Wagner from Bert Wagner

Watch this week’s episode on YouTube. A long time ago I built an application that captured user input. One feature of the application was to compare the user’s input...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Stacking Data
I have this data frame in R: > top.two player.name Team.name batting.avg hits strike.outs 1 DJ LeMahieu NYY 0.336 101 46 2 Rafael Devers BOS 0.322 100 56 I now run this: > compare.the.toptwo <- stack(top.two, select=c(batting.avg, hits, strike.outs)) What is returned?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

The Client Key

Which key is stored on the client, or accessed by the client, and used for the encryption/decryption operations in Always Encrypted?

Answer: The Column Master Key

Explanation: In Always Encrypted, the Column Master Key is managed on the client. The Column Encryption Key is stored on the server in an encrypted format. Ref: Always Encrypted - https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

Discuss this question and answer on the forums

Featured Script

Script DB Level Permissions v4.5

S. Kusen from SQLServerCentral.com

Script database and object level permissions for all database users

/*
This script will script the role members for all roles on the database.
This is useful for scripting permissions in a development environment before refreshing
development with a copy of production. This will allow us to easily ensure
development permissions are not lost during a prod to dev restoration.
URL for this script: http://www.sqlservercentral.com/scripts/login/138379/
Old URL http://www.sqlservercentral.com/scripts/Security/71562/ -- Periodically, updates are made to this script so check out the URL for updates.
Author: S. Kusen
Updates:
2019-06-10 v4.5:
1. T. Bradley suggested fix for verifying that role permissions and execute rights on new roles included.
Line 302 updated to include the type R, as:
AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group
2018-06-06 V4.4:
1. Incorporated bshimonov's suggestion to not create the dbo user since it is unnecessary.
2017-07-10 v4.3:
1. Incorporated Andrew G's updates from previous feedback (Much delayed to being updated on the main script page). Thanks Andrew!
2. danmeskel2002 recommended a fix for the SID issue for "SQL User without login".
Changed this line:
SID = '' + CONVERT(varchar(1000), sid)
to
SID = '' + CONVERT(varchar(1000), sid, 1)
2016-10-31: AG 1. Added extended stored procedures and system object permissions for master database in OBJECT LEVEL PERMISSIONS area by removing join to sys.objects and using functions instead
2. Added EXISTS check to all statements
3. Added CREATE ROLE before adding principals to roles
2016-08-25: AG 1. Remove default database being specified for an AD group user as this option causes a failure on create
2015-08-21:
1. Modified section 3.1 to load to a temp table and populate different users based on an error in 2005/2008 because of the update made for contained databases. Thanks to Andrew G for pointing that out.
2. Altered section 4.1 to include COLLATE DATABASE_DEFAULT in the join statement. Thanks to Andrew G and PHXHoward for pointing that out.
2015-06-30:
1. Re-numbered all sections based on additional updates being added inline.
2. Added sections 8, 8.1; From Eddict, user defined types needed to be added.
3. Added sections 4, 4.1; From nhaberl, for orphaned users mapping (if logins don't exist, they will not be created by this script).
4. Updated section 3.1; From nhaberl, updated to include a default schema of dbo.
2014-07-25: Fix pointed out by virgo for where logins are mapped to users that are a different name. Changed ***+ ' FOR LOGIN ' + QUOTENAME([name]) +*** to ***+ ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) +***.
2014-01-24: Updated to account for 2012 contained db users
2012-05-14: Incorporated a fix pointed out by aruopna for Schema-level permissions.
2010-01-20: Turned statements into a cursor and then using print statements to make it easier to
copy/paste into a query window.
Added support for schema level permissions
Thanks to wsoranno@winona.edu and choffman for the recommendations.
*/
SET NOCOUNT ON
/*Prep statements*/
IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_statements
CREATE TABLE ##tbl_db_principals_statements (stmt varchar(max), result_order decimal(4,1))
IF ((SELECT SUBSTRING(convert(sysname, SERVERPROPERTY('productversion')), 1, charindex('.',convert(sysname, SERVERPROPERTY('productversion')))-1)) > 10)
EXEC ('
INSERT INTO ##tbl_db_principals_statements (stmt, result_order)
SELECT
CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + SPACE(1) + '', SID = '' + CONVERT(varchar(1000), sid, 1) + SPACE(1) + '' END; '')
ELSE (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' FOR LOGIN '' + QUOTENAME(suser_sname([sid])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '')
END AS [-- SQL STATEMENTS --],
3.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */
AND NAME NOT IN (''guest'',''dbo'')')
ELSE IF ((SELECT SUBSTRING(convert(sysname, SERVERPROPERTY('productversion')), 1, charindex('.',convert(sysname, SERVERPROPERTY('productversion')))-1)) IN (9,10))
EXEC ('
INSERT INTO ##tbl_db_principals_statements (stmt, result_order)
SELECT (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' FOR LOGIN '' + QUOTENAME(suser_sname([sid])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '') AS [-- SQL STATEMENTS --],
3.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */
AND NAME NOT IN (''guest'',''dbo'')')
--SELECT * FROM ##tbl_db_principals_statements
DECLARE
@sql VARCHAR(2048)
,@sort INT
DECLARE tmp CURSOR FOR
/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
1.1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB USER CREATION *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT
[stmt],
3.1 AS [-- RESULT ORDER HOLDER --]
FROM ##tbl_db_principals_statements
--WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
WHERE [stmt] IS NOT NULL
UNION
/*********************************************/
/********* MAP ORPHANED USERS *********/
/*********************************************/
SELECT '-- [-- ORPHANED USERS --] --' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'ALTER USER [' + rm.name + '] WITH LOGIN = [' + rm.name + ']',
4.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
Inner JOIN sys.server_principals as sp
ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid
WHERE rm.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
AND rm.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME([name],'''') COLLATE database_default + ') IS NULL' + SPACE(1) + 'CREATE ROLE'
+ SPACE(1) + QUOTENAME([name]),
5.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals
WHERE [type] ='R' -- R = Role
AND [is_fixed_role] = 0
--ORDER BY [name] ASC
UNION
SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(rm.member_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') COLLATE database_default + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') COLLATE database_default AS [-- SQL STATEMENTS --],
5.2 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
)
--ORDER BY rm.role_principal_id ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
7.1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +
CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(perm.major_id)) + '.' + QUOTENAME(OBJECT_NAME(perm.major_id)) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
7.2 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
/* No join to sys.objects as it excludes system objects such as extended stored procedures */
/* INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
*/
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE /* Include System objects when scripting permissions for master, exclude elsewhere */
( DB_NAME() <> 'master' AND perm.major_id IN (SELECT [object_id] FROM sys.objects WHERE type NOT IN ('S'))
OR DB_NAME() = 'master'
)
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
/*********************************************/
/********* TYPE LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- TYPE LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +
CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(tp.schema_id)) + '.' + QUOTENAME(tp.name) --select, execute, etc on specific objects
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
8.1 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN
sys.types AS tp
ON perm.major_id = tp.user_type_id
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
UNION
SELECT '' AS [-- SQL STATEMENTS --],
9 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +
CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO 
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group
UNION
SELECT '' AS [-- SQL STATEMENTS --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
12 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(grantee_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +
CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO 
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
12.1 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.major_id = s.schema_id
inner join sys.database_principals dbprin
on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --]
OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
FETCH NEXT FROM tmp INTO @sql, @sort
END
CLOSE tmp
DEALLOCATE tmp
IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_statements

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

3 varbinary(max) columns in one table - I am creating a vendor table for internal use. The table has three varbinary(max) fields along with a few other columns. I am concerned about performance issues with the three varbinary(max) fields in one table. I know there is an 8,000 byte limit per page and if our varbinary(max) fields exceed this limit there will […]

SQL Server 2017 - Development

Conditional Stop of Azure SSIS Integration Runtime - If you use SSIS on Azure, you'll know that you get charged for all of the time that the SSIS IR is running. So I have created Data Factory pipelines to turn the SSIS IR on and off when needed. But, just in case I forget, or something goes wrong, I'd like to create a […]

SQL Server 2016 - Development and T-SQL

How to Synchronize Two Microsoft SQL Databases on different servers? - I have a Windows VPS where I am running a script with MSSQL database. In order to have a real time / online backup, I am ready to get another (similar) VPS from a different hosting provider to setup an online one-way sync of the data (script & MSSQL) to the 2nd server. So that […]

tuning sql statement which ran for an hour - Hi All, Trying to delete rows from a table. it took 1 hour. It is deleting around 60K rows from 1 specific table. I see high waits for memory, pageiolatch_ex. other thing is that, I dont see any blocking as such during that 1 hour. sql stmt ====== (@P1 bigint) DELETE FROM "dbo"."<>" WHERE "<>" […]

How can I change the credit card numbers during the INSERT ? (e.g.I want all 0s) - Hi, I have an application pushing data from source SQL database to an external SQL database. I have only access to the external (destination) database. I want to make sure there is no valid credit card number in the external database, how can I achieve this in the external database? I initially thought I could […]

Need Query for hour - I have table which has startdate which captures data every hour I need to get alert if startdate data didn't capture data more than 2 hour. How should I right query and send alert if data didn't receive more than 2 hours

add prefix to numeric values - I have a column named TPN which is a varchar type. 90% of the data are numeric values 5-9 in length. In some rows where there is no numeric values, some rows may have character values i.e. "TPN" , "TBC", NULL, empty string value I want to pad all numeric values with prefix of zeros […]

Administration - SQL Server 2014

Model New User After Existing User - I've been searching all over for a script that will allow me to create a new login by giving it an existing login to generate all the database mappings and associated permissions. The existing user could be a member of database roles or have specific object permissions within various databases. Does anyone have a script […]

Frequent disconnectity from Application - Hi , We have two app servers Aap3 & Aap4.(Microsoft Dynamics app). The Database server is SQL Server 20014. Standard edition. The application accsessing the multiple dbs in db server. From couple of days users complaining that they facing frequent disconnectivity to SQL. From Microsoft Dynamics the connection string time out setting is 30sec. In […]

Development - SQL Server 2014

Create sequencial serial numbers - hi everybody, I have a app that the previous tech develop for generating QR labels This app was created to have multiple partnumber's generate the qr string. Since in the beginning was only one workstation for partnumber the sequence generate was fine because he use the following string in the app to get and generate […]

SQL 2012 - General

Developers SSIS licencing - We have an enterprise licence, can we install using the Enterprise key SSIS onto the developers machines or do they need a seperate developer licence?

Integration Services

Import of .dtsx files - Hi all, I have about 30 packages to import into a 2016 server, that come from 2008. However each package takes about 20-25 steps to import, I'm thinking there has to be a more automated path... ..any ideas? Thanks, JB

Hardware

Help on building a new Server - I am in the process of building a new server to run our SQL Server databases (we have many TB's of data). I am looking for some help on what I should request for hard drives. My thought was to have 5 logical drives: Drive 1: System drive Drive 2: Database drive for DB's with […]

Anything that is NOT about SQL!

How to permanently pin query windows in SSMS? - My version of SSMS is 17.9.1. It can display only up to 4 tab headers horizontally. In most of the cases I need more than that. What I do is I go through all and pin all of them. They appear in 2 or 3 or even more rows. But is there a setting in […]

SQLServerCentral.com Website Issues

How To Change Forum Profile Username - Apologies if this has been asked before. I have tried searching the forum, but the hits were for SQL Server authentication generally, not for the SSC forum. I recently changed employer. I have updated the e-mail address in my profile, but I cannot update the username field in the profile. The username is the same […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.