the database principal owns a service in the database -- can't drop a user RRS feed

  • Question

  • This is version  10.0.2531.0.

    I believe I restored (or more likely attached) a database from a non-Express version, and this has caused me some problems.  For example I lost the Identity attribute on all the columns that had it.

    I had MyUser on the original db.  On the new one, there is no login for MyUser, but I still see it as a User in the database.  I try to delete it, and I get the message shown above.

    The event log is piling up with messages associated with MyUser, but I can't understand what they are.

    How can I delete this user?



    Monday, June 21, 2010 7:10 PM


All replies

  • There are two options here.


    1. You map orphan user to login if user is required. sp_change_users_login

    2. Run below code and check which service user owns.

    select as ServiceName
    from	sys.database_principals p 
    		inner join s on p.principal_id = s.principal_id 
    where = 'nimit' --User Name (which you trying to delete)

    It will give you list of Services Owned by that User. Now to remove this dependancy you have to ALTER SERVICE AUTHORIZATION by running below query. If there are multiple services than run below code for all services.


    --Here nimit is the ServiceName I got from above query
    ALTER AUTHORIZATION ON Service::nimit TO [dbo] --Transfer Service Authorization to dbo


    Hope This Helps!



    • Proposed as answer by Umair ME Tuesday, November 19, 2013 10:24 AM
    Tuesday, June 22, 2010 12:58 AM
  • Thanks Nimit,

    I tried this, and the service name is SqlQueryNotificationService, with a guid at the end of the name.  I can't figure out a syntax that works.  It doesn't like the hyphens in the guid, and it doesn't work if I put single quotes around the name.


    I have many services with a similar name.  Almost all are assigned to NT Authority.


    So I still need some help.



    Tuesday, June 22, 2010 2:00 AM
  • Hi Jim,

    You should check the MyUser login on new server before executing the sp_change_users_login SP.

    if MyUser login is not there on the new server, you need to prepare the Login script for Old server and run on New server.

    To create the login scripts on old server go through the following link.

    How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008 :





    RajaSekhar Reddy .K


    Tuesday, June 22, 2010 4:20 AM
  • Ok, I went through this process, and I ran sp_change_users_login, but I'm no closer to being able to remove the user.  Perhaps it's no longer causing exceptions, but I'd still like to get rid of it.


    Wednesday, June 23, 2010 12:56 AM
  • Have to tried to use square brackets in ALTER AUTHORIZATION command . [enteryourservicename_with its_GUID]
    Thanks, Leks
    Wednesday, June 23, 2010 2:23 AM

    As Lekss suggested you have to give service name in Square brackets like below.

    ALTER AUTHORIZATION ON Service::[nimit] TO [dbo]




    Wednesday, June 23, 2010 10:50 PM
  • Yep, that got it.

    Thanks all,


    Thursday, June 24, 2010 11:52 AM
  • thanks, Do you  know if we can put multiple  tables in  single brackets[ ]

    Tuesday, August 6, 2019 5:30 AM
  • thanks, Do you  know if we can put multiple  tables in  single brackets[ ]

    If you have a question, it is a lot better to start a new thread, explaining your problem from start to end. Piggybacking on a nine year old is meaningless.

    But the answer to your question is no. The brackets serves to delimit a single identifier.

    Erland Sommarskog, SQL Server MVP,

    Tuesday, August 6, 2019 9:26 PM