Export (0) Print
Expand All
Expand Minimize

Rename user sys

SQL Server 2014

Upgrade Advisor detected the user name sys in a database. This name is reserved. Rename the user before you upgrade. If the user is not renamed, the database will be in a suspect state after the upgrade process and will be unavailable until the database is brought online.

Database Engine

User sys is reserved.

Before Upgrade Procedure

Before you upgrade, in each database that contains user sys, do the following:

  1. Create a new user.

  2. Use the following statements to display all permissions that are granted by user sys and granted to user sys.

    -- Return permissions granted by user sys.
    SELECT * FROM sysprotects WHERE grantor = USER_ID('sys')
    -- Return permissions granted to user sys.
    SELECT * FROM sysprotects WHERE uid = USER_ID('sys')
    
  3. To transfer ownership of all objects owned by sys to the new user, use sp_changeobjectowner.

  4. Drop user sys.

  5. To restore the original permissions captured in step 2, use the AS new_user clause of the GRANT statement.

  6. Modify scripts to reference the new user. For example, scripts that contain statements such as SELECT * FROM sys.my_table must be changed to SELECT * FROM new_user.my_table.

After Upgrade Procedure

If the user sys was not renamed prior to upgrading, do the following:

  1. Execute the statement ALTER DATABASE db_name SET ONLINE. The database will be in SINGLE_USER mode.

  2. Follow all steps in the Before Upgrade Procedure section.

  3. Execute the statement ALTER DATABASE db_name SET MULTI_USER.

Community Additions

ADD
Show:
© 2014 Microsoft