{"id":4369,"date":"2013-02-01T14:49:43","date_gmt":"2013-02-01T14:49:43","guid":{"rendered":"http:\/\/craftydba.com\/?p=4369"},"modified":"2013-02-06T02:35:53","modified_gmt":"2013-02-06T02:35:53","slug":"user-design-with-ssms","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=4369","title":{"rendered":"User Design 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. I will be going over sections 1F to 1H in the sample code.<\/p>\n

I want to explore how the CREATE LOGIN<\/a>, DROP LOGIN<\/a>, CREATE USER<\/a>, and DROP USER<\/a> statements can be executed within SSMS.<\/p>\n

Security in Microsoft SQL Server is based upon the idea that principals are given rights to securables. Principals<\/a> in the most basic sense are server logins and database users. <\/p>\n

Securables<\/a> are any object such as table, view, function, procedure, and etc in which a database user can be GRANTED, DENIED, or REVOKED rights. <\/p>\n

Please see the MSDN permissions hierarchy diagram<\/a> below.<\/p>\n

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

Our task today is to add two server logins named [AUTOS_USER] which has read rights to the database and [AUTOS_ADMIN] which has read & write rights to the database. <\/p>\n

To create a new server login, find the logins node under server security in the object explorer. Right click and select New Login.<\/p>\n

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

First<\/span>, lets take a look at the detailed choices that need to be made for the [AUTOS_USER]. <\/p>\n

Make sure the radio button for SQL Server Authentication is selected. Enter the correct login name and password. Make sure the password options are unselected since we do not want to enforce password policy, enforce password expiration or make the user change the password at next login. Last but not least, make sure the default database is [AUTOS]. <\/p>\n

We now supplied enough information for SSMS to create a server login (CREATE LOGIN<\/a>).<\/p>\n

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

Find the user mapping page on the right side of the new login dialog box. <\/p>\n

Map the new login to the [AUTOS] database as a user. As a default, the user will be in the public database role. Add the user to the db_datareader role and map the user to the [ACTIVE] schema. This can be done by entering the correct schema name or using the search boxes to bring up a list of schemas.<\/p>\n

We now supplied enough information for SSMS to create a database user (CREATE USER<\/a>).<\/p>\n

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

Second<\/span>, we need to repeat the same detailed choices for the [AUTOS_ADMIN] server login. <\/p>\n

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

We need to repeat the same detailed choices for the [AUTOS_ADMIN] database user. <\/p>\n

The only changes are a default schema of [STAGE] and a additional database role of db_datawriter.<\/p>\n

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

Let’s take a look at the results of our hard labor. <\/p>\n

Go to the Logins node under Server Security in the object explorer. Expand the logins node to see all the members. Both the [AUTOS_ADMIN] and [AUTOS_USERS] server login do exist.<\/p>\n

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

To find out if we created our database users correctly, drill into the [AUTOS] database in the object explorer pane. Go to the Users node under database Security. Expand the users node to see all the members. Both the [AUTOS_ADMIN] and [AUTOS_USERS] database users do exist.<\/p>\n

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

To complete our test of designing users with SSMS, we need to drop the database user then server login. <\/p>\n

Find the [AUTOS_USER] database user under the logins node in the object explorer. Right click and select delete. The delete object dialog box will appear. Selecting the OKAY button will remove the database user.<\/p>\n

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

I will leave deleting the [AUTOS_USER] server login as an exercise for you to-do.<\/p>\n

In summary, we can do many of the Data Definition Language (DDL<\/a>) tasks that can be done in T-SQL. The cool script button shows you what SQL Server Management Studio (SSMS<\/a>) will use for code to accomplish a particular task. Next time, I will be talking about creating user defined tables 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":[56,59,391,749,58,60,12,15,750,751,28,741,470],"class_list":["post-4369","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-create-login","tag-create-user","tag-ddl","tag-designing-users","tag-drop-login","tag-drop-user","tag-free-code","tag-john-f-miner-iii","tag-principles","tag-securables","tag-sql-server","tag-sql-server-management-studio","tag-ssms"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4369","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=4369"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4369\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4369"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4369"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4369"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}