SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Implement Azure SQL Elastic Jobs with PowerShell

Mikey Bronowski from SQLServerCentral

In this article, I will work you through the process of creating elastic jobs in Azure using PowerShell. The elastic jobs are similar to regular agents jobs in SQL Server although they add extra functionality like for example dedicated target groups that can be used in the job step level. Currently, the elastic jobs are […]

How DevOps can enable the evolving insurance landscape

Additional Articles from Redgate

The insurance landscape is evolving, and customer demands are far from where they used to be, with some of the biggest changes happening in the last year. James Boother from COEO covers how database DevOps can enable the evolving insurance landscape.

Move SQL Server Files for Master Database

Additional Articles from MSSQLTips.com

In this second part of this article we look at how to move the SQL Server master database files to a different location.

From the SQL Server Central Blogs - CROSS vs OUTER APPLY

Kenneth.Fisher from SQLStudies

I love CROSS APPLY. I also love OUTER APPLY. What’s the difference though? The same difference as with an INNER ... Continue reading

From the SQL Server Central Blogs - Data Fabric defined

James Serra from James Serra's Blog

Another buzzword that you may have been hearing a lot about lately is Data Fabric. In short, a data fabric is a single environment consisting of a unified architecture...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Granting Schema Permissions
What is the format for granting permissions on a schema, such as this: GRANT SELECT ON xxxx TO MyUser If the schema name is abc, what replaced the xxxx above?
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)

Default $DebugPreference

What is the default value for the $DebugPreference variable in Powershell?

Answer: SilentlyContinue

Explanation: The default is SilentlyContinue. Ref: _about_Preference_Variables - https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_preference_variables?view=powershell-7.1

Discuss this question and answer on the forums

Featured Script

Script DB Level Permissions v4.6

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:
2021-06-10 v4.7: SK updates from various feedback
1. Using SID from AG update noted in his 2020-07-07 update. SUSER_SNAME([sid]) used instead of SUSER_SNAME([name])
2. Fixed TYPE syntax thanks to Fran4mat
3. Added SQL MI users and groups per suggestion from Dromero22
2020-07-07 v4.6: AG added database owner and fix database_principals that are named differently to AD (use latest name from AD/Windows, not SQL value)
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.
2018-11-06 v4.51: AG added ALTER USER as sp_revokeaccess fails due to a user owning a schema with tables. Drop fails and requires the SQL user to be remapped
Added commented script section to allow dropping of all user permissions in restored database
2017-07-10 v4.3:
I was unable to easily get this into a stored procedure / powershell script, so this update includes the changes/updates noted here:
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.
Thanks to wsoranno@winona.edu and choffman for the recommendations.
*/
SET NOCOUNT ON
print '
/***************************************************************************/
/***************************************************************************
/* Delete existing users in database (so that they can be recreated with different permissions) */
USE [' + DB_NAME() +']
DECLARE @UserName nvarchar(256)
DECLARE csrUser CURSOR FOR
SELECT [name] FROM sys.database_principals WHERE principal_id > 4 AND is_fixed_role < 1 ORDER BY [name] OPEN csrUser FETCH NEXT FROM csrUser INTO @UserName WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
EXEC sp_revokedbaccess @UserName
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
FETCH NEXT FROM csrUser INTO @UserName
END
CLOSE csrUser DEALLOCATE csrUser
***************************************************************************/
/***************************************************************************/'
/*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 [type] IN (''U'', ''S'', ''G'')
THEN
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 SUSER_SNAME([sid]) FROM sys.database_principals WHERE SUSER_SNAME([sid]) = '' + SPACE(1) + '''''''' + SUSER_SNAME([sid]) + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + SPACE(1) + '', SID = '' + CONVERT(varchar(1000), sid, 1) + SPACE(1) + '' END; '')
ELSE
CASE WHEN rm.name = ''dbo'' /* dbo "name" can be different to Windows User */
THEN ''ALTER AUTHORIZATION ON DATABASE::'' + QUOTENAME(DB_NAME()) + '' TO '' + QUOTENAME(SUSER_SNAME([sid])) + '';''
ELSE (''IF NOT EXISTS (SELECT SUSER_SNAME([sid]) FROM sys.database_principals WHERE SUSER_SNAME([sid]) = '' + SPACE(1) + '''''''' + SUSER_SNAME([sid]) + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' 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 ELSE ALTER USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' WITH LOGIN = '' + QUOTENAME(SUSER_SNAME([sid])) + '';'')
END
END
WHEN [type] IN (''E'', ''X'')
THEN
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 SUSER_SNAME([sid]) FROM sys.database_principals WHERE SUSER_SNAME([sid]) = '' + SPACE(1) + '''''''' + SUSER_SNAME([sid]) + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + SPACE(1) + '', SID = '' + CONVERT(varchar(1000), sid, 1) + SPACE(1) + '' END; '')
ELSE
CASE WHEN rm.name = ''dbo'' /* dbo "name" can be different to Windows User */
THEN ''ALTER AUTHORIZATION ON DATABASE::'' + QUOTENAME(DB_NAME()) + '' TO '' + QUOTENAME(SUSER_SNAME([sid])) + '';''
ELSE (''IF NOT EXISTS (SELECT SUSER_SNAME([sid]) FROM sys.database_principals WHERE SUSER_SNAME([sid]) = '' + SPACE(1) + '''''''' + SUSER_SNAME([sid]) + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' 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
END
END AS [-- SQL STATEMENTS --],
3.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN (''U'', ''S'', ''G'', ''E'', ''X'') /* windows users, sql users, windows groups, external users, external groups */
AND NAME NOT IN (''guest'')')
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 SUSER_SNAME([sid]) 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 ELSE ALTER USER '' + SPACE(1) + QUOTENAME([name]) + '' WITH LOGIN = '' + QUOTENAME(suser_sname([sid])) + '';'')
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
/*********************************************/
/********* DB SCHEMA CREATION *********/
/*********************************************/
SELECT '-- [-- DB SCHEMAS --] --' AS [-- SQL STATEMENTS --],
3.5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF SCHEMA_ID(' + QUOTENAME([name],'''') COLLATE database_default + ') IS NULL' + SPACE(1) + 'EXEC (' + '''' + 'CREATE SCHEMA'
+ SPACE(1) + QUOTENAME([name]) +
'''' + ')',
3.51 AS [-- RESULT ORDER HOLDER --]
FROM sys.schemas
WHERE [name] not in (
/*exclude built-in schemas*/
'dbo',
'guest',
'INFORMATION_SCHEMA',
'sys',
'Logging',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter'
)
--ORDER BY [name] ASC
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', 'E', 'X') -- windows users, sql users, windows groups, external users, external 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
AND [name] NOT IN ('public','dbo','guest','INFORMATION SCHEMA','sys')
--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', 'E', 'X') -- S = SQL user, U = Windows user, G = Windows group, E = external user, X = external 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 TYPE::' + 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', 'E', 'X') -- S = SQL user, U = Windows user, G = Windows group, E = external user, X = external 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 - Development

deadlock prevention - Hi All, I' m looking for some help in interpreting and fixing the deadlock. Recently we started seeing some deadlocks and this is one among them. Attaching the xml deadlock graph and table info. SQL Server version ================ Microsoft SQL Server 2017 (RTM-CU23) Enterprise Edition: Core-based Licensing (64-bit) Regards, Bob

SQL Server 2016 - Administration

dm_exec_connections EE - Is there a mapping column in extended event for encrypt_option in sys.dm_exec_connections . my scenario is to capture sessions that are not encrypted.

Performance Counters Missing in SQL 2016 SP2 - Hi, I'm looking for a fix to an issue where performance counters are missing from SQL 2016 (SP2). All SQL related counters are available in the OS i.e. Perfmon but only and small amount related to XTP are available in SQL itself e.g. if I run the following SELECT * FROM sys.dm_os_performance_counters It returns 57 […]

SQL Server 2016 - Development and T-SQL

suggestions on making more efficient and faster - I have a piece of code that I would like some suggestions on how to make it faster\ more efficient. The Quality and Quality_Detail are properly Indexed, but are Large tables. Thanks. BEGIN TRY -- Revision 1 - Added SELECT @Total_Readings = SUM(CAST(QD.Value AS float)) FROM Quality Q INNER JOIN Quality_Detail QD ON (Q.Quality_ID […]

Rounding question - If i execute query 1, i get 0.00036369128107, but if i execute query 2, I get 0.00000000000000. What i want it is I want the results (i.e non zero) but with 6 decimal places. How can i accomplish this? So, I'm looking for something like 0.000363 or 0.000364. Any idea how to accomplish this? select […]

SQL Server 2012 - T-SQL

Pivot table syntax (?) issue - Please delete post, I was looking at the goal of the query totally wrong.

SQL Server 2019 - Administration

What's the difference with installs for Core vs Server/Cal? - Does anyone know why under MS vol licensing there are SQL 2019 Std installs for Core and different ones for Server/CAL. The filename and size is the same, what's different about these? We are converting Server/CAL to Core. Does it matter what install was used when the server was built?

SQL Server 2019 - Development

Looking for a way to change all objects at once? - Hi, I have a field in a table called prin_ball, upon doing a search for this, I would have to change it in about 158 different objects (that is jobs tables, procedures, views, and triggers). Is there anyway of doing a find and replace to change all at once. Or at least the views, tables […]

Mirror Database without Domain - Hello, I tried to make a mirror for my Demo database My Principal server is in the Domain by the name ‘server1’ (192.168.0.0/24) My Witness server is in the same domain and same network (192.168.0.0/24) with the server1 and the name is ‘server2’ My Mirror server is in another network (192.168.10.0/24), which is connected with […]

Reporting Services

Add On-Demand Subreport - This is so very easy to do in Crystal Reports. You add subreport hidden anywhere on a report, link it to the parameters on main report and you can just double-click on it to run. In Report Builder, I've added the subreport, linked the parameters but then it runs for every group. I then set […]

SSRS 2012

Total of the row - Hi all, I have 4 columns in the report: Customer, Price, Cost, Revenue. I would like to put in the end of the report total value of each column (Price, Cost, Revenue). I clicked the right button to the appropriate column and chose to "add a total". When I select all customer values ??in the […]

Powershell

PowerShell script help - Hi Guys, I am executing a PowerShell script from SSIS (Execute Process Task). Simple script, Calling PS script and returning a variable value. The process runs successfully when I am running on my local machine. However, when I run on the server, the "SSIS Package" hangs on "Execute Process Task". When I run on my […]

Design Ideas and Questions

Vendors Database - Dear all, We are looking for web based system through which we can administer a large number of vendors from all over the world. For each vendor, we want to add information such as - country of origin - rates - team members - previous projects -etc - some information in a memo field From […]

Anything that is NOT about SQL!

Forum bug with the "like" button - I was looking at a post, specifically this one: Performance Counters Missing in SQL 2016 SP2 I thought that the answer (the first reply) was a good one, so I clicked to "like". What I expected to happen was what I'd seen on other ones that I'd liked and that after a minute or so, […]

Events

EightKB... I'm presenting! Bring your water-cooled helmet! :D - EightKB... I'm presenting! If you've not seen this one before, it will totally blow your mind. To summarize, in one of the most heterodixic SQL presentations you're likely to ever see, I destroy the myth of Random GUID fragmentation, show how they can be used to actually prevent fragmentation, and lay waste to what people […]

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.