locked
Slash User Access (dbo rights) RRS feed

  • Question

  • Hello,

    Back in 2009, we would grant dbo rights though a SQL command which would result in sysusers having slash \AADD\UserName accounts. if we queried sysusers at the database level, the results shows \AADD\Username user mappings.

    Does anyone know of a SQL commend that will remove these old legacy aliased "dbo" network accounts from sysusers?

    Any help would be greatly appreciated.

    Deb

    Tuesday, June 30, 2020 7:30 PM

All replies

  • Did you try

    DROP USER [\AADD\UserName]


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 30, 2020 9:18 PM
  • Hi DKSTRAUB,

    >Does anyone know of a SQL commend that will remove these old legacy aliased "dbo" network accounts from sysusers?

    Same reply from this case: drop-user-slash-user-from-database

    Migrate the current database to 2008, delete user, migrate back.

    Note:
    In SQL Server, the login name (Server Login) is separate from the database user (database User), but it is also related. So the correct posture: After deleting the login name (login), you must also go to each database and delete the corresponding user (user). Before deleting the login name, you must check that whether there are OWNER of those jobs or OWNER of the database for the Windows authentication Account (NT account) or not, otherwise you will encounter some problems later:

    1.If the OWNER of the job is not modified before deleting the Windows authenticated user (if the OWNER of this job is this Windows user, then after deleting the Windows authenticated user, the job will report an error similar to the following:

    The job failed. The owner (xx\xxx) of job syspolicy_purge_history does not have server access.

    Therefore, before deleting a Windows authenticated user, you must check and modify the owner of the job to avoid this situation.

    2.Before deleting the Windows authenticated user, confirm whether there is a database OWNER for this Windows authenticated user or not. Otherwise, an error will be reported when deleting the login name

    Msg 15174, Level 16, State 1, Line 4
    Login'xxx\xxxx' owns one or more database(s). Change the owner of the database(s) before dropping the login.

    You must modify the owner of the database (generally change the owner of the database to sa) to delete the login name

    sp_changedbowner'sa'

    3.Sometimes when deleting a user, the following error is reported, and the corresponding user can only be deleted after modification.

    Encountered the following error:

    Msg 15138, Level 16, State 1, Line 3

    The database principal owns a schema in the database, and cannot be dropped.

    USE YourSQLDba;
    GO
    ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbo];

    USE [YourSQLDba]
    GO
    DROP USER [xxx\xxx];
    GO

    Of course, according to the actual situation:

    USE [UserDatabase];
    GO
    ALTER AUTHORIZATION ON SCHEMA::[xxx] TO [dbo];

    And this may be helpfuldrop-sql-server-login-dependencies

    BR,

    MIAOYUXI

    Wednesday, July 1, 2020 2:09 AM
  • Hi DKSTRAUB,

    Is the reply helpful?

    Is the issue solved?

    Choose to "Mark as Answer"  for the helpful reply and that will be helpful for others.

    BR,

    MIAOYUXI

    Thursday, July 2, 2020 1:22 AM
  • Hi DKSTRAUB,

    Is the reply helpful?

    Do you solve the issue?

    You can choose to "Mark As Answer" for the helpful reply, this will benefit for others.

    BR,

    MIAOYUXI

    Tuesday, July 7, 2020 1:24 AM
  • Hi DKSTRAUB,

    Is the issue solved?

    BR,

    MIAOYUXI

    Thursday, July 9, 2020 2:40 AM
  • Hi,

    I'm following this open case,is the issue solved?

    You can choose to  mark the helpful reply to close this case.

    BR,

    MIAOYUXI

    Friday, July 24, 2020 9:21 AM