This week has been full of excitement here at cbtr.net. We had ssis packages that were being executed via a sql jobs. These jobs were taking forever to finish. For all our other servers, there’s a Long Running Job setup via DPA to monitor but since many developer uses SSIS and DPA can’t send alerts to specific group of people, the alert was disabled.
Today, I got a message from a dev telling me that some of his packages were running for a long time, 18 hours long (that’s what she said), and it should of taken < 30 seconds (that’s not what she said).
Last week, apparently I messed up revoking some permission and ended up denying it instead so this week, I’m having to go find which user has denied permission when it should of been revoke. The following query got me thru it:
SELECT USER_NAME(grantee_principal_id) AS ‘User’
, state_desc AS ‘Permission’
, permission_name AS ‘Action’
, CASE class
WHEN 0 THEN ‘Database::’ + DB_NAME()
WHEN 1 THEN OBJECT_NAME(major_id)
WHEN 3 THEN ‘Schema::’ + SCHEMA_NAME(major_id)
END AS ‘Securable’
FROM sys.database_permissions dp
WHERE class IN ( 0, 1, 3 )
AND minor_id = 0
AND state_desc = ‘deny’
This morning, I was working with a dev. He was trying to use SQL Compare and it said he didn’t have view definition permission. I was looking thru the security group for the specific database and could identify which group he was in. I ran the following script and was able to find him:
EXECUTE AS LOGIN = ‘domainA\UserA’
SELECT * FROM fn_my_permissions (NULL, ‘DATABASE’);
SELECT * FROM sys.user_token
The user_token view will tell you what group or role the user is part off. The fn_my_permissions(null,’database’) will tell you what permissions you have for that specific database.
Today, a biztalk dev sent me two queries asking me why one work and one didn’t. I asked him if it was a test.
select uidInstanceID FROM [SF1XXXXX02\BIZTALK].[BizTalkMsgBoxDb].[dbo].[Instances] with (nolock) <– Works
select uidInstanceID FROM [bizDNSsql01].[BizTalkMsgBoxDb].[dbo].[Instances] with (nolock) <–don’t work
A few days ago, a coworker brought online a new AlwaysOn Availability Group (aoag) on sql 2012. She added both nodes (node1 and node2) via the gui. Everything completed okay. Then we looked at the Available Replica hive and found that the second node had a little red ‘x’ next to it. It said something along the line of node1 not able to connect to node2. After some looking around, I found that the endpoint wasn’t present.
I had her create the endpoint on both SQL servers and that fixed the problem