This is one I haven't seen before. I'm trying to drop a database user and got the below message. The login doesn't own any roles or schemas or other objects and everything I google on the error comes up with "principal owns a schema" which is not what this error is. When I try googling database services, I get SQL Server level services or Windows services. Nothing helpful.
I've even checked all system tables with "service" in the name and all the principal IDs = dbo, not this user's principal ID.
Has anyone ever seen a "principal owns a service in the database" error inside a drop database user attempt?
Any advice on finding out what this service might be?
April 19, 2024 at 2:43 pm
April 19, 2024 at 4:21 pm
That did not come up in Google. I just looked at it, and the principal ID for all lines is dbo, not the SQL login in question.
Is "name" in that alter script supposed to be a literal or a "replace this"?
April 26, 2024 at 4:13 pm
Likely referring to a MSMQ service in the database, query the catalogs to find out which, the following should help
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Recreated this by creating a test SP, queue and service (owned by user test)
Try to drop user and get same message box, this will identify the service name and owning user
select name, USER_NAME(PRINCIPAL_ID) AS OwningUser
from sys.services
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 5:17 pm
EXCELLENT. That gave me exactly what I needed. Vended solution services somehow set up inside of SQL Server.
Now I just need to figure out how to fix the problem, which won't be as easy as setting authorization to dbo. I need to figure out if there's a different account to set it to.
Thank you so much, everyone!
May 1, 2024 at 5:21 pm
And the user just informed me that two of the four lines I found are "old" services. Which makes me wonder if this isn't the source of her testing pain.
May 1, 2024 at 6:18 pm
And the user just informed me that two of the four lines I found are "old" services. Which makes me wonder if this isn't the source of her testing pain.
in my experience some devs and users are not greating at house keeping
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 6:21 pm
True 'dat!
May 1, 2024 at 6:29 pm
Glad you got it resolved Brandie
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply