Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart.
However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same results.
Today, I am going to continue the series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script can be done with SSMS.
I am going off topic to demonstrate table permissions. Thus, the code for this topic is shown in the examples below.
I want to explore how the GRANT, DENY and REVOKE statements for administering table permissions can be executed within the SSMS graphical interface.
Let’s start by granting [DELETE] rights to the [guest] account for the [ACTIVE].[MAKES] table via a T-SQL statement. The guest account is useful for users that have a server login but do not have explicit rights to the database. We can view the results of this action on the permissions page in the table properties dialog box.
The total opposite permission is to deny [DELETE] rights to the [guest] account for the [ACTIVE].[MAKES] table via a T-SQL statement. We can view the results of this action on the permissions page in the table properties dialog box. Remember, any deny rights on a permission change trump any other grants.
Last but not least, let’s revoke the [DELETE] rights for the [guest] account on the [ACTIVE].[MAKES] table via a T-SQL statement. We can view the results of this action on the permissions page in the table properties dialog box.
So far, I have shown how to GRANT, DENY and REVOKE table permissions via T-SQL. These same rights can be administered by the DBA using the SSMS graphical user interface.
Again, open up the table properties dialog box and chose the permissions page. Click the search button to find and verify the guest account. In this example, we could easily choose a windows domain account which was assigned a server login and database user.
After selecting the database user, chose the rights you want to GRANT, DENY, or REVOKE. We will give the [guest] user [DELETE] rights. The WITH GRANT check box allows a principle (user) to give out rights to other principles (users).
In summary, we can perform many of the Data Definition Language (DDL) tasks with the SQL Server Management Studio (SSMS) graphical interface. I just went over the GRANT, DENY, and REVOKE commands to assign table permissions to users. These same steps can be used with other database objects such as VIEWS.
Next time, I will be talking about Defining Columns with SSMS.
