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

    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

Answers

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 s.name as ServiceName
    from	sys.database_principals p 
    		inner join sys.services s on p.principal_id = s.principal_id 
    where p.name = '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!

     

    Thanks,
    Nimit

    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.

     

    Jim

    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 : http://support.microsoft.com/kb/918992/en-us

     

    --------------------------------

    Thanks,

     

    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.

    Jim

    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]

     

    Thanks,

    Nimit

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

    Thanks all,

    Jim

    Thursday, June 24, 2010 11:52 AM