{"id":82044,"date":"2009-03-13T00:38:23","date_gmt":"2009-03-13T00:38:23","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73225"},"modified":"2018-12-12T13:32:43","modified_gmt":"2018-12-12T13:32:43","slug":"granting-rights-to-all-objects-in-a-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/granting-rights-to-all-objects-in-a-database\/","title":{"rendered":"Granting rights to all objects in a database"},"content":{"rendered":"

File this under the \u201cI can\u2019t believe there is still stuff that I keep learning about SQL Server 2005!\u201d though thankfully most things I find I learn are things I wouldn\u2019t be all that likely to use.<\/em><\/p>\n

I was asked today how I felt about using the syntax:<\/p>\n

GRANT EXECUTE TO [username]<\/p>\n

to give users rights to all procedures in the database (and you could use it with SELECT, INSERT,\u2026and other rights too).  Well, first off, I had to admit that I didn\u2019t know you could do this. I knew you could grant execute rights to a user on a schema, but not the entire database. Jasper Smith had an article back in 2004 on sqldbatips that covered it (http:\/\/www.sqldbatips.com\/showarticle.asp?ID=8<\/a>), so it isn\u2019t some big secret.<\/p>\n

I often use the technique to grant a user all rights to a given schema:<\/p>\n

GRANT EXECUTE on SCHEMA::schemaName TO [username]<\/p>\n

I often use the this technique to apply execute rights to an application login\/users to a given schema. I don\u2019t like it as<\/strong><\/em> much when using the dbo schema, since it commonly contains other object that I don\u2019t want to just give blanket rights to, but when using named schemas to segregate objects into functional groups (partially for security, and partly for logical separation) I feel it is a good idea.<\/p>\n

Even using the dbo schema isn\u2019t horrible, as long as you understand what you are doing and are careful to separate out other procedures. I am a big believer in having the database be as self contained as possible, so I try to put maintainence objects and such in the database, typically in a schema named utility. Often this might have a procedure to drop all foreign keys, or indexes, etc.  Whatever I might need during a data load operation, or even just general maintenance. So granting rights to the entire db seems a bit too lenient as I don\u2019t want ANYONE who isn\u2019t the dba running these procedures.<\/p>\n

I guess the fact is that I think that the database security should be a bit more stringent than a simple GRANT all rights to EVERYTHING, as you have to be cognizant that sometimes there will be objects in the database that just shouldn\u2019t be opened up for the programmer to accidentally use, thinking that an object does something different than it actually does.  When you carefully lay out schemas, odds are that you are considering the purpose\/meaning of the schemas and have  a plan for the schemas which SHOULD consider security\/usage as well.  <\/p>\n","protected":false},"excerpt":{"rendered":"

File this under the \u201cI can\u2019t believe there is still stuff that I keep learning about SQL Server 2005!\u201d though thankfully most things I find I learn are things I wouldn\u2019t be all that likely to use. I was asked today how I felt about using the syntax: GRANT EXECUTE TO [username] to give users……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82044","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82044","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\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82044"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82044\/revisions"}],"predecessor-version":[{"id":82356,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82044\/revisions\/82356"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82044"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}