Solved

Trying to delete Login from SQL Server, getting error

Posted on 2016-09-15
19
123 Views
Last Modified: 2016-11-02
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:  

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.microsoft.com/Forums/en-US/3a337af8-cc60-4c66-a475-bd0965576545/the-database-principal-is-set-as-the-execution-context-of-one-or-more-procedures-?forum=sqlsecurity) but haven't found anything that helps yet.
0
Comment
Question by:Jamie Fellrath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
19 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799780
I never liked to use the GUI to perform those kind of tasks.
Try to open a new query window and run the following command:
DROP LOGIN [Domain\login_name]

Open in new window

0
 
LVL 1

Author Comment

by:Jamie Fellrath
ID: 41799811
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.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799815
Can you provide a print screen of the login in the SQL Server security / login folder?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Jamie Fellrath
ID: 41799842
Do you mean the "Properties" window?
0
 
LVL 1

Author Comment

by:Jamie Fellrath
ID: 41799851
Here's the screen print fragment of the login:  

btempleton.png

Is this what you mean? I'm sorry, but I'm not comfortable showing more than this login.
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 145 total points
ID: 41799855
Sure. It's that login that's important for this question.
So you ran this? DROP LOGIN [OTTA\btempleton]

If affirmative what was exactly the error message?
0
 
LVL 1

Author Comment

by:Jamie Fellrath
ID: 41799867
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.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 355 total points
ID: 41799871
It seems that there is a trigger in the database that is executed impersonating this user or there is a job created that it owns or setup for email notification. You will have to look in all these places and make sure you remove/replace any reference to it. You also have to remove the user form ALL the databases.
1
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799884
Check in the SQL Server instance / Server Objects / Triggers. It's some trigger created there?
0
 
LVL 1

Author Comment

by:Jamie Fellrath
ID: 41799911
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.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 355 total points
ID: 41799919
In a trigger you can have something like:

CREATE TRIGGER trg_name ON tbl_name
WITH EXECUTE AS 'user'
for insert, update
...

If you can't see anything like that you are safe. Error is caused by those agent ownership and maybe email notifications for jobs.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 145 total points
ID: 41799991
Yes, must be the EXECUTE AS in some trigger or event notification. Check if some of those objects has it.
0
 
LVL 1

Author Comment

by:Jamie Fellrath
ID: 41800319
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?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41800378
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.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 355 total points
ID: 41800394
Also you can try this, if you haven't yet. Maybe you missed something:

http://www.jasonstrate.com/2013/07/security-questions-removing-logins-from-databases/
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 355 total points
ID: 41800396
This will help you to find jobs with a certain owner:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 355 total points
ID: 41800398
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41822342
Please give some feedback, award points if the answers were helpful or close this question if not.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question