Feeds:
Posts
Comments

Archive for November, 2019

Interesting one today:

On a lab machine, when CHECKDB was executed, this seemingly tricky error popped up. This was a lab machine, and there was no traffic or any one connected to it.

Code that we ran:

DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
	, TABLOCK
GO
Error Message:

Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively 
locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot 
could not be created and the database or table could not be locked. 
See Books Online for details of when this behavior is expected and 
what workarounds exist. Also see previous errors for more details.

After checking that there was no traffic or any connections to the database, I re-ran the same CHECKDB a few more times and still ran into the same error.

Reading this MSDB article, some ideas came to mind. The two conditions mentioned in the article are not the root of the problem.

  • At least one other connection is using the database against which you run the DBCC CHECK command.
  • The database contains at least one file group that is marked as read-only.

Once the TABLOCK is removed, the command worked.

DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »

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.

--
-- Detailed Permissions<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>
--
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]
		, CASE P.class_desc
			WHEN 'CERTIFICATE' THEN C.name
			WHEN 'SYMMETRIC_KEYS' THEN S.name
			WHEN 'TYPE' THEN T.name
			ELSE O.name
		  END									AS [ObjectName]
		, O.type_desc							AS [Detailed_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
LEFT OUTER JOIN sys.types AS T
	ON T.user_type_id = P.major_id
WHERE P.major_id &gt;= 0      --     Ignore system objects
ORDER BY P.class_desc, L.name ASC
GO

DetailedPermissions

Hope this helps,
_Sqltimes

Read Full Post »