These will probably be ‘old hat’ for DBA’s but as a systems engineer I found them very useful.
I lay no claim to any of these scripts or understand them fully in any way! just passing on useful information.
This one from:
(C) 2007-2012, Adam Machanic
Feedback: mailto:amachanic@gmail.com
Updates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
“Beta” Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx
Has been particularly useful in the past at routing out problems in the DB…Who Is Active
dbo.sp_WhoIsActive.sql.xml (166.6 KB)
Add the above script to your SQl then run the following.
sp_whoisactive {this invokes a detailed list of what jobs are active on the server.}
select * from device where DeviceID = xxx {this digs into the database to find out what device a particular device id is associated with}
These little nuggets of information (thank you AirWatch) are very useful if you try to add a user and the console reports back ‘user already exists’ but you can not see it in the console. You possibly will find that you have an orphaned user account in the DB, this will pull the user account info from the DB
SELECT eu.UserName
FROM mobileManagement.EnrollmentUser eu
WHERE UserName IS NOT NULL AND LocationGroupID IN (SELECT Childlocationgroupid FROM dbo.LocationGroupFlat WHERE Parentlocationgroupid = xxx)
Group BY eu.UserName
HAVING COUNT(eu.UserName) > 1;
AND
select cdeu.DeviceID, eu.*
from [mobileManagement].[EnrollmentUser] eu
Left join [mobileManagement].[CurrentDeviceEnrollmentUser] cdeu on cdeu.EnrollmentUserID = eu.EnrollmentUserID
where eu.UserName like ‘%enrolmentusername%’
–EXEC [mobileManagement].[EnrollmentUser_Delete] @EnrollmentUserID = xxxx
It is easy to find the location croup id, user or device id from the console, point to the device, user or location group, right mouse and then inspect element, you will see the group, device or user id there.
Also I have had occasionally an Orphaned Admin user account, the following script will identify the Admin account and delete it.
Select * from CoreUser where UserName = ‘AdminAcct’
the first column returned is the CoreUserId
exec dbo.coreuserdelete CoreUserID will delete the Admin account.