Jamie Fellrath
asked on
Trying to delete Login from SQL Server, getting error
Hi all,
I'm trying to delete a user from our MS SQL Server 2008 (yeah, I know) database server. The user in question has left the organization and is no longer in Active Directory, but apparently his name is still all over the place.
I've removed him from every database, and now I'm trying to delete him in SSMS in Object Explorer via <database name>/Security/Logins. It's giving me the following error:
I've removed him everywhere I could find him - jobs, etc. Anyone have any other ways to find what setting he might be in, still? I've tried a couple of queries from TechNet and such (example: https://social.technet.mic rosoft.com /Forums/en -US/3a337a f8-cc60-4c 66-a475-bd 0965576545 /the-datab ase-princi pal-is-set -as-the-ex ecution-co ntext-of-o ne-or-more -procedure s-?forum=s qlsecurity ) but haven't found anything that helps yet.
I'm trying to delete a user from our MS SQL Server 2008 (yeah, I know) database server. The user in question has left the organization and is no longer in Active Directory, but apparently his name is still all over the place.
I've removed him from every database, and now I'm trying to delete him in SSMS in Object Explorer via <database name>/Security/Logins. It's giving me the following error:
The server principal is set as the execution context of a trigger or event notification and cannot be dropped. (Microsoft SQL Server, Error: 15186)
I've removed him everywhere I could find him - jobs, etc. Anyone have any other ways to find what setting he might be in, still? I've tried a couple of queries from TechNet and such (example: https://social.technet.mic
ASKER
Thanks Vitor!
This doesn't seem to be working - apparently because the Login includes a backslash (due to its being an Active Directory login with domain specified). The SQL doesn't care for the backslash.
This doesn't seem to be working - apparently because the Login includes a backslash (due to its being an Active Directory login with domain specified). The SQL doesn't care for the backslash.
Can you provide a print screen of the login in the SQL Server security / login folder?
ASKER
Do you mean the "Properties" window?
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, same error:
Msg 15186, Level 16, State 1, Line 1
The server principal is set as the execution context of a trigger or event notification and cannot be dropped.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Check in the SQL Server instance / Server Objects / Triggers. It's some trigger created there?
ASKER
Thanks Zberteoc. I've got him removed from all the databases already, including the system databases. I also checked the jobs... at least for him as the owner (and found a couple and changed the owner). I'll check around more for the email notifications.
Vitor - I see a trigger there but don't see how to find the owner of it.
Vitor - I see a trigger there but don't see how to find the owner of it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I used our Redgate SQL Search tool to look for his name anywhere - and aside from notes made in old procedure code and the like, I'm not seeing anything. Assuming that the Triggers we're talking about are coded as owned in the EXECUTE AS clause, then I think we've exhausted that possibility.
What about event notifications? Where might I look to find some of that stuff?
What about event notifications? Where might I look to find some of that stuff?
Look for the login name, specifically, but only for the btempleton part.
For notification look In the jobs. The jobs have Notification in case of failure and those can be tight with accounts. I am not 100% if are in connection with logins or just emails but you can check in the jobs' Properties and then in Alerts or Notification.
For notification look In the jobs. The jobs have Notification in case of failure and those can be tight with accounts. I am not 100% if are in connection with logins or just emails but you can check in the jobs' Properties and then in Alerts or Notification.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please give some feedback, award points if the answers were helpful or close this question if not.
Try to open a new query window and run the following command:
Open in new window