Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
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 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.

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.

© 2015 Microsoft