SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Identify Unused Tables in SQL Server and Azure SQL

Aleksey Vitsko from SQLServerCentral

This article presents a way to discover those tables that are unused over a period of time, along with suggestions on how to get rid of these tables.

No, Accelerated Database Recovery Doesn’t Fix NOLOCK.

Additional Articles from Brent Ozar Blog

I have never seen a T-SQL feature that people love as much as NOLOCK.

From the SQL Server Central Blogs - The Mystery of the Locked-Up Database -Scooby Dooing Episode 6

SQLEspresso from SQLEspresso

No Scooby-Doo story is complete without footprints leading to a hidden passage. In SQL Server 2025, those footprints point us straight toward the next big feature: optimized locking. And...

From the SQL Server Central Blogs - Set Theory vs. Batch Mode in SQL Server

SQLPals from Mission: SQL Homeostasis

Set Theory vs. Batch Mode in SQL Server

Not long ago, a colleague of mine was completely shocked when he first heard about SQL Server’s Batch Mode feature. His immediate...

Introduction to PostgreSQL for the data professional

Site Owners from SQLServerCentral

Adoption and use of PostgreSQL is growing all the time. From mom-and-pop shops to large enterprises, more data is being managed by PostgreSQL. In turn, this means that more data professionals need to learn PostgreSQL even when they have experience with other databases. While the documentation around PostgreSQL is detailed and technically rich, finding a simple, clear path to learning what it is, what it does, and how to use it can be challenging. This book seeks to help with that challenge.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Estimated Rows
I have two calls to the GENERATE_SERIES TVF in this code: SELECT TOP 10 gs.value FROM GENERATE_SERIES(1, 10) AS gs ORDER BY NEWID () OPTION (RECOMPILE); go DECLARE @a int = 10; SELECT TOP (@a) gs.value FROM GENERATE_SERIES(1, @a) AS gs ORDER BY NEWID () OPTION (RECOMPILE); In the actual query plans, what is the estimated number of rows for each batch?
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)

PWDCompare

What does the PWDCOMPARE function do?

Answer: This compares a plaintext password to a password hash to determine if they are the same

Explanation: This function allows you to compare a plaintext password against a hash to determine if they are the same. Ref: PWDCOMPARE - https://learn.microsoft.com/en-us/sql/t-sql/functions/pwdcompare-transact-sql?view=sql-server-ver17

Discuss this question and answer on the forums

Featured Script

Bat and SQL script to generate DB user permissions for all Databases in SQL server

Srinivas Merugu from SQLServerCentral

Get a list of all user permissions in a database

--***********--BAt Script-------------
@echo off
:: ================================
:: Run SQL script on all databases
:: ================================
:: Usage: RunSQLAllDBs.bat "C:pathtoscript.sql"
if "%~1"=="" (
echo Usage: %~nx0 "D:SQLDBACoforgeAutomationUSerPermissionSecBackup.sql"
exit /b 1
)
set "SQLSCRIPT=%~1"
set "SERVERNAME=VASHIANVIINST2"
set "OUTPUTDIR=D:SQLDBACoforgeAutomationUSerPermissionAllDBPermission"
:: Create output folder if not exists
if not exist "%OUTPUTDIR%" mkdir "%OUTPUTDIR%"
echo Getting database list from %SERVERNAME%...
:: Generate database list (excluding system DBs)
sqlcmd -S %SERVERNAME% -E -h -1 -W -Q "SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb')" > "%OUTPUTDIR%dblist.txt"
echo Running script on each database...
for /f "usebackq delims=" %%D in ("%OUTPUTDIR%dblist.txt") do (
echo ===============================================
echo Running on database: %%D
echo ===============================================
sqlcmd -S %SERVERNAME% -E -d %%D -i "%SQLSCRIPT%" -o "%OUTPUTDIR%%%D.sql"
)
echo Done! Output files are in:
echo %OUTPUTDIR%
pause
--------************User Permission script**********
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 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 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
6 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 --],
8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --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 --],
9 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
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 usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 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 --],
12 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') -- S = SQL user, U = Windows user, G = Windows group
UNION
SELECT '' AS [-- SQL STATEMENTS --],
13 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 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 --],
15 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

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 2019 - Development

extended events to spy on our server between 8 and 9 am - Hi, our peer who owns a remote mysql server from which we extract warehouse data daily between 8 and 9am thru ssis says there was nothing unusual nor any missing indexes this morning when querying one part of his database timed out with the error you see below. Our dba says it was mysql's fault. […]

visual studio seems to lose "provider" on pkg imports - hi, just to get this part out of the way, if my intended target for an ssis project is 2019, i always change the project over to 2019 before starting whatever i'm going to do. Locally and on my vm i work in vs 2022. It might be just as of late, when i import […]

Editorials

The Improvement Limit - Comments posted to this topic are about the item The Improvement Limit

Technological Dinosaurs or Social Dinosaurs? - Comments posted to this topic are about the item Technological Dinosaurs or Social Dinosaurs?

Job Postings

SQL Server DBA - Immediate need - Immediate need for a hands-on SQL Server DBA. This is a greenfield opportunity to help a fast-growing company develop custom solutions for its key systems from the ground up. The initial contract term is 6 months (extendable). Must be currently located in the U.S. and authorized to work for any employer in the U.S. We […]

Article Discussions by Author

AlwaysON health check alerts - Comments posted to this topic are about the item AlwaysON health check alerts

Bat and SQL script to generate DB user permissions for all Databases in SQL server - Comments posted to this topic are about the item Bat and SQL script to generate DB user permissions for all Databases in SQL server

PWDCompare - Comments posted to this topic are about the item PWDCompare

Migrate SSRS Reports to a New Server in Just a Few Clicks - Comments posted to this topic are about the item Migrate SSRS Reports to a New Server in Just a Few Clicks

DBCC CHECKIDENT - Comments posted to this topic are about the item DBCC CHECKIDENT

Distributed Availability Group Health: T-SQL and Zabbix - Comments posted to this topic are about the item Distributed Availability Group Health: T-SQL and Zabbix

Stairway to Azure SQL Hyperscale – Level 3: RBPEX Caching Layer Internals - Comments posted to this topic are about the item Stairway to Azure SQL Hyperscale – Level 3: RBPEX Caching Layer Internals

Vector Datatype Dimensions - Comments posted to this topic are about the item Vector Datatype Dimensions

SQL Server 2022 - Administration

Restore Job fails in Replication - Dear Folks, Restore jobs in my SQL2022 Standard edition replication were failing on a new setup on windows 2022 standard edition, 64 bits.The log has 'Access denied error' but the system admin says he has added the service account to the windows admin group..Normal copy \paste between the two server folders works well though...Any comment […]

SQL Server 2022 - Development

ERD Tools recommendation - Hey All, I'm looking for a free ERD tool. Does anyone have any recommendations? Thanks!

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.