What exactly does `GRANT, REVOKE, and DENY Permissions on System Objects` mean?



  • Link: https://docs.microsoft.com/en-us/sql/relational-databases/databases/manage-metadata-when-making-a-database-available-on-another-server?view=sql-server-ver15#grant-revoke-and-deny-permissions-on-system-objects

    I understand that:

    1. Server level logins, roles and securable permissions are stored in the master database.
    2. Database level users, roles and securable permissions are stored in the respective user database.

    This script ( https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances ) allows me to transfer the server level logins, roles and securable permissions.

    Does it cover GRANT, REVOKE, and DENY Permissions on System Objects? If not, then what exactly does GRANT, REVOKE, and DENY Permissions on System Objects mean and how do I extract these?



  • That script will transfer the Server level logins (#1), but not the database permissions (#2). It's done that way because the database may not exist on the destination server. System Objects typically refers to the system tables, like Master.dbo.sysobjects. If the login has permissions to that on the source server, then this script will transfer those permissions to the destination server.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2