Feeds:
Posts
Comments

Archive for May, 2019

Quick one today:

Quite often, we need to review and security in all our production and non-production environments to be sure that each account has the least number of permissions it needs to perform its operations (BEST PRACTICE)

As part of it, we gather this data occasionally for further review.

Gather all users in each database and their role assignments (both fixed roles & user defined roles).

--
-- List all users in each database and their role assignments
--
SELECT DB_NAME(), RP.type_desc AS [RoleType], RP.name, UP.name, UP.type_desc [UserType], UP.default_schema_name AS [User_Schema]
FROM sys.database_role_members AS R
INNER JOIN sys.database_principals AS UP
	ON UP.principal_id = R.member_principal_id
INNER JOIN sys.database_principals AS RP
	ON RP.principal_id = R.role_principal_id
ORDER BY RP.name, UP.name
GO

Once we wrap this into sp_MSforEachDB procedure, then we could run it for all databases.

--
-- List all users in each database and their role assignments
--
EXEC sp_msforeachdb 'USE [?];
	SELECT DB_NAME(), RP.type_desc AS [RoleType], RP.name, UP.name, UP.type_desc [UserType], UP.default_schema_name AS [User_Schema]
	FROM sys.database_role_members AS R
	INNER JOIN sys.database_principals AS UP
		ON UP.principal_id = R.member_principal_id
	INNER JOIN sys.database_principals AS RP
		ON RP.principal_id = R.role_principal_id
	ORDER BY RP.name, UP.name
'
GO

It you use multi-script, we could gather this data across all instances in your environment as well.

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »

Interesting error today:

When we query over linked server, we run into some restrictions. One such restrictions is this, where we could not query XML columns over LinkedServer connection.

For example, if the remote server table has a XML column in its structure, when we query the remote server for this table, we run this error:

--
-- Old query that throws this error
--
SELECT ID, XMLColumn
FROM LinkedServer01.DatabaseName.dbo.TableName
GO
Error : Msg 9514, Level 16, State 1, Line 1 
Xml data type is not supported in distributed queries. 
Remote object has xml column(s)

Resolution:

Not sure why this limitation exists, but there is a workaround: OPENQUERY

In the past, we’ve covered some key benefits of OPENQUERY and how it allows us to circumvent some limitations of remote-table-value function calls. Today, we’ll see another benefit of OPENQUERY in circumventing the XML limitation of linked servers.

Rather than querying XML directly, we convert XML into NVARCHAR(MAX) and revert it back to XML once the data is on local server.

--
-- Modified query to circumvent the limitation
--
SELECT ID, CONVERT(XML, XMLColumn) AS XMLcolumn
FROM OPENQUERY(LinkedServer01,
	'SELECT ID
		 , CONVERT(NVARCHAR(MAX), XMLColumn) AS XMLColumn
	FROM DatabaseName.dbo.TableName') AS RemoteQuery
GO

The XML data is retrieved as NVARCHAR(MAX) to the local machine; And immediately converted to XML before displaying.

 

Hope this helps,
_Sqltimes

Read Full Post »