Understanding how to provide users access to various objects in a database is an important topic for anyone administering SQL Server. Often times a developer or an analyst may need additional permissions granted on a database in order to perform their work. From my experience this type of request usually comes when the user has been assigned a new project and needs visibility to a database that they have never worked with before.
Security should always be a top priority. Understanding why a user needs access to something is vital as it allows you to make sure that you are providing the appropriate permissions without being too restrictive or too broad when assigning the new permissions.
With that in mind, I will illustrate the best way to grant a user VIEW DEFINITION permissions for both individual databases as well as all databases on the server. For reference, VIEW DEFINITION allows a user to see the metadata of the securable to which the permission is granted. It does not actually allow the user to access the securable itself.
When a user does not have sufficient VIEW DEFINITION permissions on a particular database they will get an error similar to what is shown below.
In this example I attempted to execute the command against the OperationRef database without sufficient VIEW DEFINITION permissions. Though the error message could be a little more specific, it clearly lets you know something is wrong.
The scripts outlined below will give you a good idea as to how to appropriately grant the necessary VIEW DEFINITION permissions to allow users to see the database object definitions.
First we’ll start with the most broad option for granting access. In order to grant VIEW DEFINITION for all databases to a specific user (we’ll call him Pedro) use the following script.
The next, more common, option would be to grant access for the user for a specific database. The following script is similar to the first one but requires you to specify the database in the command as well as drop the option from the command.
Keeping track of permission changes is also important. If you want to review which users have permissions you can use the command. This will return the assigned permissions for each user based on the database from which it is run.
For example if I wanted to look for VIEW DEFINITION permissions assigned to the JDev database I would execute the following script.
The output will look something like this. If you’re looking specifically for VIEW DEFINITION permissions you can look for it under the Action column.
Now that you understand how to the necessary permissions lets briefly discuss how to those permissions. Doing this is just as straight forward as the steps outlined above.
If you want to revoke VIEW DEFINITION permissions to the user across all databases the following script will accomplish this.
Similarly, if you want to revoke VIEW DEFINITION permissions to the user for a specific database, use the following script.
I hope this has helped shed some light on how to and permissions in SQL Server. Though this specifically references the VIEW DEFINITION permission set this logic can be applied to many different objects within SQL Server.
Paragraphs & Topic Sentences
A paragraph is a series of sentences that are organized and coherent, and are all related to a single topic. Almost every piece of writing you do that is longer than a few sentences should be organized into paragraphs. This is because paragraphs show a reader where the subdivisions of an essay begin and end, and thus help the reader see the organization of the essay and grasp its main points.
Paragraphs can contain many different kinds of information. A paragraph could contain a series of brief examples or a single long illustration of a general point. It might describe a place, character, or process; narrate a series of events; compare or contrast two or more things; classify items into categories; or describe causes and effects. Regardless of the kind of information they contain, all paragraphs share certain characteristics. One of the most important of these is a topic sentence.
A well-organized paragraph supports or develops a single controlling idea, which is expressed in a sentence called the topic sentence. A topic sentence has several important functions: it substantiates or supports an essay’s thesis statement; it unifies the content of a paragraph and directs the order of the sentences; and it advises the reader of the subject to be discussed and how the paragraph will discuss it. Readers generally look to the first few sentences in a paragraph to determine the subject and perspective of the paragraph. That’s why it’s often best to put the topic sentence at the very beginning of the paragraph. In some cases, however, it’s more effective to place another sentence before the topic sentence—for example, a sentence linking the current paragraph to the previous one, or one providing background information.
Although most paragraphs should have a topic sentence, there are a few situations when a paragraph might not need a topic sentence. For example, you might be able to omit a topic sentence in a paragraph that narrates a series of events, if a paragraph continues developing an idea that you introduced (with a topic sentence) in the previous paragraph, or if all the sentences and details in a paragraph clearly refer—perhaps indirectly—to a main point. The vast majority of your paragraphs, however, should have a topic sentence.
Most paragraphs in an essay have a three-part structure—introduction, body, and conclusion. You can see this structure in paragraphs whether they are narrating, describing, comparing, contrasting, or analyzing information. Each part of the paragraph plays an important role in communicating your meaning to your reader.
Introduction: the first section of a paragraph; should include the topic sentence and any other sentences at the beginning of the paragraph that give background information or provide a transition.
Body: follows the introduction; discusses the controlling idea, using facts, arguments, analysis, examples, and other information.
Conclusion: the final section; summarizes the connections between the information discussed in the body of the paragraph and the paragraph’s controlling idea.
The following paragraph illustrates this pattern of organization. In this paragraph the topic sentence and concluding sentence (CAPITALIZED) both help the reader keep the paragraph’s main point in mind.
SCIENTISTS HAVE LEARNED TO SUPPLEMENT THE SENSE OF SIGHT IN NUMEROUS WAYS. In front of the tiny pupil of the eye they put, on Mount Palomar, a great monocle 200 inches in diameter, and with it see 2000 times farther into the depths of space. Or they look through a small pair of lenses arranged as a microscope into a drop of water or blood, and magnify by as much as 2000 diameters the living creatures there, many of which are among man’s most dangerous enemies. Or, if we want to see distant happenings on earth, they use some of the previously wasted electromagnetic waves to carry television images which they re-create as light by whipping tiny crystals on a screen with electrons in a vacuum. Or they can bring happenings of long ago and far away as colored motion pictures, by arranging silver atoms and color-absorbing molecules to force light waves into the patterns of original reality. Or if we want to see into the center of a steel casting or the chest of an injured child, they send the information on a beam of penetrating short-wave X rays, and then convert it back into images we can see on a screen or photograph. THUS ALMOST EVERY TYPE OF ELECTROMAGNETIC RADIATION YET DISCOVERED HAS BEEN USED TO EXTEND OUR SENSE OF SIGHT IN SOME WAY.
George Harrison, “Faith and the Scientist”
In a coherent paragraph, each sentence relates clearly to the topic sentence or controlling idea, but there is more to coherence than this. If a paragraph is coherent, each sentence flows smoothly into the next without obvious shifts or jumps. A coherent paragraph also highlights the ties between old information and new information to make the structure of ideas or arguments clear to the reader.
Along with the smooth flow of sentences, a paragraph’s coherence may also be related to its length. If you have written a very long paragraph, one that fills a double-spaced typed page, for example, you should check it carefully to see if it should start a new paragraph where the original paragraph wanders from its controlling idea. On the other hand, if a paragraph is very short (only one or two sentences, perhaps), you may need to develop its controlling idea more thoroughly, or combine it with another paragraph.
A number of other techniques that you can use to establish coherence in paragraphs are described below.
Repeat key words or phrases. Particularly in paragraphs in which you define or identify an important idea or theory, be consistent in how you refer to it. This consistency and repetition will bind the paragraph together and help your reader understand your definition or description.
Create parallel structures. Parallel structures are created by constructing two or more phrases or sentences that have the same grammatical structure and use the same parts of speech. By creating parallel structures you make your sentences clearer and easier to read. In addition, repeating a pattern in a series of consecutive sentences helps your reader see the connections between ideas. In the paragraph above about scientists and the sense of sight, several sentences in the body of the paragraph have been constructed in a parallel way. The parallel structures (which have been emphasized) help the reader see that the paragraph is organized as a set of examples of a general statement.
Be consistent in point of view, verb tense, and number. Consistency in point of view, verb tense, and number is a subtle but important aspect of coherence. If you shift from the more personal "you" to the impersonal “one,” from past to present tense, or from “a man” to “they,” for example, you make your paragraph less coherent. Such inconsistencies can also confuse your reader and make your argument more difficult to follow.
Use transition words or phrases between sentences and between paragraphs. Transitional expressions emphasize the relationships between ideas, so they help readers follow your train of thought or see connections that they might otherwise miss or misunderstand. The following paragraph shows how carefully chosen transitions (CAPITALIZED) lead the reader smoothly from the introduction to the conclusion of the paragraph.
I don’t wish to deny that the flattened, minuscule head of the large-bodied "stegosaurus" houses little brain from our subjective, top-heavy perspective, BUT I do wish to assert that we should not expect more of the beast. FIRST OF ALL, large animals have relatively smaller brains than related, small animals. The correlation of brain size with body size among kindred animals (all reptiles, all mammals, FOR EXAMPLE) is remarkably regular. AS we move from small to large animals, from mice to elephants or small lizards to Komodo dragons, brain size increases, BUT not so fast as body size. IN OTHER WORDS, bodies grow faster than brains, AND large animals have low ratios of brain weight to body weight. IN FACT, brains grow only about two-thirds as fast as bodies. SINCE we have no reason to believe that large animals are consistently stupider than their smaller relatives, we must conclude that large animals require relatively less brain to do as well as smaller animals. IF we do not recognize this relationship, we are likely to underestimate the mental power of very large animals, dinosaurs in particular.
Stephen Jay Gould, “Were Dinosaurs Dumb?”
SOME USEFUL TRANSITIONS
(modified from Diana Hacker, A Writer’s Reference)
- To show addition:
- again, and, also, besides, equally important, first (second, etc.), further, furthermore, in addition, in the first place, moreover, next, too
- To give examples:
- for example, for instance, in fact, specifically, that is, to illustrate
- To compare:
- also, in the same manner, likewise, similarly
- To contrast:
- although, and yet, at the same time, but, despite, even though, however, in contrast, in spite of, nevertheless, on the contrary, on the other hand, still, though, yet
- To summarize or conclude:
- all in all, in conclusion, in other words, in short, in summary, on the whole, that is, therefore, to sum up
- To show time:
- after, afterward, as, as long as, as soon as, at last, before, during, earlier, finally, formerly, immediately, later, meanwhile, next, since, shortly, subsequently, then, thereafter, until, when, while
- To show place or direction:
- above, below, beyond, close, elsewhere, farther on, here, nearby, opposite, to the left (north, etc.)
- To indicate logical relationship:
- accordingly, as a result, because, consequently, for this reason, hence, if, otherwise, since, so, then, therefore, thus
Produced by Writing Tutorial Services, Indiana University, Bloomington, IN