Feeds:
Posts
Comments

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
GO

DetailedPermissions

Hope this helps,
_Sqltimes

 

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'
				ELSE 'UNKNOWN'
		  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)
GO

Result looks something like this:

Distributor_AgentProfiles.PNG

Hope this helps,
_Sqltimes

Interesting one today:

This may be common knowledge, but upon running some tests its evident that Snapshot Replication does both the things:

  1. Deliver a complete snapshot (of all articles chosen) to the Subscribers
  2. Also delivers, incremental changes to those articles a.k.a INSERTS/UPDATES / DELETES

Or let’s state it differently:

  1. Snapshot and Transactional replication are two different replication types
  2. When we set up replication, we create:
    1. LogReader Agent : Reads TRN Log at Publisher and writes changes to Distributor
    2. Snapshot Agent: Creates a complete snapshot/backup/data of Publisher database to be created at Subscriber (but does not deliver it to Subscriber)
    3. Distributor Agent: This agent delivers data created in the above two steps to the Subscriber.
      1. Delivers the continuous changes that LogReader brings
      2. Delivers snapshot that Snapshot agent creates
  3. Transactional Replication:
    1. When we create Transactional Replication, the continuous activity at Publisher is replicated to Subscriber. This is carried out by LogReader Agent & Distributor agent.
      1. Distributor has replication procedures for INSERT, DELETE & UPDATE that take each DML activity at Publisher and re-play it as Subscriber
    2. But Transaction replication expects database & tables to already exist at the Subscriber
      1. And that the object structure matches — as the replication procedures (INSERT & UPDATE) depend on the Table & Column names and column order
    3. Creating Snapshot Agent is an optional step while establishing Transactional Replication. Its not needed to create Snapshot Agent, if the database objects already exist at Subscriber;
    4. And all we care about is replicating data from this point onward (and not the old data)
  4. Snapshot Replication:
    1. As part of creating Snapshot replication we create Snapshot Agent & Distributor
    2. We create all replication procedures (for each article) to replicate incremental changes on top of initial snapshot/backup/data

Summary

  • Yes, when we set up Snapshot replication, not only is the initial data from Publisher is replicated to the Subscribers, it also replicates incremental changes happening at Publisher to all Subscribers.
  • Snapshot Agent is different Snapshot Replication

 

Hope this helps,
_Sqltimes

 

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
ORDER BY @@SERVERNAME, DB_NAME(), O.name, C.name
GO

 

Result

Output will look something like this:

IDENTITY_Properties

Hope this helps,
_Sqltimes

 

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
GO

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

 

Hope this helps,
_Sqltimes

 

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).

Example:

From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.

Resolution:

First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.

 

SETSPN -L <SQL Server Instance Service Account>

 

If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:

 

Kerberos Tool Output

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

Valid SPN

 

Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA

 

 

Hope this helps,
_Sqltimes