Archive for the ‘TSQL’ Category

Interesting one today:

Quite often, we need a quick way to check if correct permissions are granted on the database objects. Since there could be thousands of such objects with hundreds of different possible permissions, this gets a bit daunting.

In the past, we’ve seen some dynamic between Roles, Logins, Users & Permissions. Today, we’ll look into a quick way to a query to retrieve permissions on all objects for all principals; Then we can filter further and focus on the objects/logins we need.

SELECT    P.class_desc				AS [Permission_Category]
		, P.grantee_principal_id	AS [Principal_ID]
		, L.name					AS [Principal_Name]
		, L.type_desc				AS [Principal_Type]
		, P.major_id				AS [ID_of_Resource]
		, COALESCE (O.name, S.name, C.name )AS [Object_Name]
		, O.type_desc				AS [Object_Type]
		, P.state_desc				AS [Permission]
		, G.name					AS [Who_Gave_This_Permission]

FROM sys.database_permissions AS P
INNER JOIN sys.database_principals AS L
	ON L.principal_id = P.grantee_principal_id
LEFT OUTER JOIN sys.database_principals AS G
	ON G.principal_id = P.grantor_principal_id
LEFT OUTER JOIN sys.objects AS O
	ON O.object_id = P.major_id
LEFT OUTER JOIN sys.symmetric_keys AS S
	ON S.symmetric_key_id = P.major_id
LEFT OUTER JOIN sys.Certificates AS C
	ON C.certificate_id = P.major_id

WHERE P.major_id >= 0	--	Ignore system objects
ORDER BY P.class_desc, L.name ASC


Hope this helps,


Read Full Post »

Quick one today:

In our lab, quite frequently, we need to cross check the replication distribution agent profile settings between each environment to be sure they are set correctly. Replication Monitor provides a good way to check each agent and its profiles, but when you have many, many distribution agents in each environment, manual check is not fun.

This could be done by collecting all agent profile parameters using T-SQL

-- Run this query pointed to the Distributor
SELECT    D.id
		, D.name
		, D.publisher_database_id
		, D.publisher_id
		, D.publisher_db
		, D.publication
		, D.subscriber_id
		, D.subscriber_db
		, D.subscription_type
		, D.profile_id
		, D.creation_date
		, D.subscriber_security_mode
		, D.subscriptionstreams

		, CASE	WHEN P.agent_type = 1 THEN 'Snapshot Agent'
				WHEN P.agent_type = 2 THEN 'Log Reader Agent'
				WHEN P.agent_type = 3 THEN 'Distribution Agent'
				WHEN P.agent_type = 4 THEN 'Merge Agent'
				WHEN P.agent_type = 9 THEN 'Queue Reader Agent'
				ELSE 'Unknown'
		  END AS [AgentType]
		, P.profile_name
		, CASE	WHEN P.type = 0 THEN 'System Profile'
				WHEN P.type = 1 THEN 'User Defined Profile'
		  END AS [ProfileType]

		, PML.parameter_name
		, PM.value AS [ParameterValue_InUse]

		, PML.default_value
		, PML.min_value
		, PML.max_value

FROM dbo.MSdistribution_agents AS D
INNER JOIN msdb.dbo.MSagent_profiles AS P
	ON D.profile_id = P.profile_id
	AND P.agent_type = 3 -- Distribution agent

INNER JOIN msdb.dbo.MSagent_parameters AS PM
	ON PM.profile_id = D.profile_id

INNER JOIN msdb.dbo.MSagentParameterList AS PML
	ON PML.agent_type = P.agent_type -- Distributor
	AND PML.parameter_name = SUBSTRING(PM.parameter_name, 2, 50)

Result looks something like this:


Hope this helps,

Read Full Post »

Quick one today:

Occasionally, we find a need to retrieve all the IDENTITY columns in all our environments to perform analysis on some architectural tickets. Since there could be many databases across different environments, we need a single script that could retrieve necessary data — so we could point to to a multi-script and BAM !! All data retrieved in one shot !!

--  Retrieve IDENTITY columns for all tables in a database
SELECT	  	  @@SERVERNAME				AS [ServerName]
		, DB_NAME()				AS [DatabaseName]
		, O.name				AS [TableName]
		, C.name				AS [ColumnName]
		, T.name 				AS [DataType]
		, I.seed_value
		, I.increment_value
		, I.last_value
		, IDENT_CURRENT(O.name) AS [IdentCurrent]
		, I.is_not_for_replication
		, I.is_nullable
		, I.column_id
		, I.user_type_id
FROM sys.objects AS O
INNER JOIN sys.columns AS C
	ON O.object_id = C.object_id
	AND O.is_ms_shipped = 0
INNER JOIN sys.identity_columns AS I
	ON C.name = I.name
	AND C.object_id = I.object_id
	AND I.is_identity = 1
INNER JOIN sys.types AS T
	ON T.user_type_id = I.user_type_id



Output will look something like this:


Hope this helps,


Read Full Post »

Quick one today:

Occasionally, we also have a need to retrieve all the server roles a login is associated with. Earlier we covered a similar post where covered a script to retrieve all database users & their database role assignments; In this we’ll  cover similar task, but for Server Roles & Logins.

-- List all Server roles a login is part of
SELECT DB_NAME(),  RP.type_desc AS [Role Type], RP.name, MP.name, MP.type_desc AS [LoginType]
FROM sys.server_role_members AS R
INNER JOIN sys.server_principals AS RP
	ON RP.principal_id = R.role_principal_id
	AND RP.type_desc = 'SERVER_ROLE'
INNER JOIN sys.server_principals AS MP
	ON MP.principal_id = R.member_principal_id

Just like last time, if you run this from multi-script window, we could compare between two different environments.


Hope this helps,


Read Full Post »

Quick one today:

Surprisingly, this need comes up frequently to list all the Sql Agent jobs in all instances in our lab machines. Sometimes we need to cross check to be sure all environments have the same jobs.

This below query lists out all jobs with their steps:

-- List all Sql Agent Jobs with their steps and other relevant information
	, L.loginname
	, S.database_name
	, J.job_id
	, J.name
	, J.enabled
	, J.description
	, S.step_id
	, S.step_name
	, S.command

FROM dbo.sysjobs_view AS J
INNER JOIN dbo.sysjobsteps AS S
	ON J.job_id = S.job_id
INNER JOIN sys.syslogins AS L
	ON L.sid = J.owner_sid
ORDER BY J.enabled ASC
	, J.originating_server ASC
	, S.database_name ASC
	, J.name ASC
	, S.step_id ASC



Hope this helps,

Read Full Post »

Interesting one today:

Earlier in our lab environment, ran into this interesting error:

2019-06-20 00:18:46.67 Connecting to Distributor ''
2019-06-20 00:18:46.78 The replication agent had encountered an exception.
2019-06-20 00:18:46.78 Source: Replication
2019-06-20 00:18:46.78 Exception Type: 
2019-06-20 00:18:46.78 Exception Message: The snapshot could not be 
generated because the publisher is inactive.
2019-06-20 00:18:46.78 Message Code: 54057
2019-06-20 00:18:46.78

Replication from publisher to subscriber was set up correctly, but when the agent runs, the SQL Agent job stops with this error.


Go to Distributor and run this query to check the status of the publisher instance.

EXEC sp_helpdistpublisher


As we can see, one of the publisher instance is set to Inactive. So now we need to reset it.

Run the script below to change the publisher status at distributor:

EXEC sp_changedistpublisher  @publisher = 'Publisher',  @property = 'active', @value = 'true'

The result looks something like this:


Now  run the previous ‘sp_helpdistpublisher’ again and now the status is active:



Hope this helps,


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
FROM LinkedServer01.DatabaseName.dbo.TableName
Error : Msg 9514, Level 16, State 1, Line 1 
Xml data type is not supported in distributed queries. 
Remote object has xml column(s)


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
FROM OPENQUERY(LinkedServer01,
	FROM DatabaseName.dbo.TableName') AS RemoteQuery

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


Hope this helps,

Read Full Post »

Older Posts »