{"id":4475,"date":"2013-02-05T03:37:44","date_gmt":"2013-02-05T03:37:44","guid":{"rendered":"http:\/\/craftydba.com\/?p=4475"},"modified":"2013-02-05T21:26:53","modified_gmt":"2013-02-05T21:26:53","slug":"administering-table-permissions-with-ssms","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=4475","title":{"rendered":"Table Permissions with SSMS"},"content":{"rendered":"

\"\"<\/a><\/p>\n

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<\/a>, 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. <\/p>\n

However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS<\/a>) has menus and dialog boxes to achieve many of the same results. <\/p>\n

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<\/a> can be done with SSMS. <\/p>\n

I am going off topic to demonstrate table permissions. Thus, the code for this topic is shown in the examples below.<\/p>\n

I want to explore how the GRANT<\/a>, DENY<\/a> and REVOKE<\/a> statements for administering table permissions can be executed within the SSMS graphical interface.<\/p>\n

Let’s start by granting [DELETE] rights to the [guest] account for the [ACTIVE].[MAKES] table via a T-SQL statement. The guest account<\/a> 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.<\/p>\n

 <\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

 <\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

 <\/p>\n

\"\"<\/a><\/p>\n

So far, I have shown how to GRANT<\/a>, DENY<\/a> and REVOKE<\/a> table permissions via T-SQL. These same rights can be administered by the DBA using the SSMS graphical user interface.<\/p>\n

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.<\/p>\n

 <\/p>\n

\"\"<\/a><\/p>\n

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).<\/p>\n

 <\/p>\n

\"\"<\/a><\/p>\n

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<\/a>.<\/p>\n

Next time, I will be talking about Defining Columns with SSMS.<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,759,12,758,15,755,760,28,741,470,761,762],"class_list":["post-4475","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-deny","tag-free-code","tag-grant","tag-john-f-miner-iii","tag-permissions-page","tag-revoke","tag-sql-server","tag-sql-server-management-studio","tag-ssms","tag-table-permissions","tag-table-properties"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4475"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4475\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}