{"id":656,"date":"2011-08-22T21:49:08","date_gmt":"2011-08-22T21:49:08","guid":{"rendered":"http:\/\/craftydba.com\/?p=656"},"modified":"2011-08-22T21:57:43","modified_gmt":"2011-08-22T21:57:43","slug":"crafting-databases-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=656","title":{"rendered":"Crafting Databases – Part 2"},"content":{"rendered":"

The CREATE LOGIN <\/a>and CREATE USER<\/a> statements are part of the Data Definition Language (DDL) defined by Codd<\/a>. Basically, a server login has to be defined so that person can connect to the server and a user login has to be created to map the server login to a particular database.<\/p>\n

I am going to continue my talk with the BSA hypothetical business problem from Part 1.<\/p>\n

Server logins allow access at the server level which can be defined using Windows Authentication or a SQL Server Authentication. Windows Authentication assumes that an existing domain account exists and is a usable state. SQL Server Authentication allows a DBA to create a login by supplying an user account name and password.<\/p>\n

The snipet below creates two logins using SQL Server Authentication. <\/p>\n

-- Which database to use.\r\nUSE [master]\r\nGO\r\n\r\n\r\n-- Delete existing login.\r\nIF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BSA_ADMIN')\r\nDROP LOGIN [BSA_ADMIN]\r\nGO\r\n\r\n-- Add new login.\r\nCREATE LOGIN [BSA_ADMIN] WITH PASSWORD=N'M0a2r0c9h11$', DEFAULT_DATABASE=[BSA]\r\nGO\r\n\r\n\r\n-- Delete existing login.\r\nIF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BSA_USER')\r\nDROP LOGIN [BSA_USER]\r\nGO\r\n\r\n-- Add new login.\r\nCREATE LOGIN [BSA_USER] WITH PASSWORD=N'M0a2r0c9h11#', DEFAULT_DATABASE=[BSA]\r\nGO<\/span><\/pre>\n<\/p>\n
A server login is not very useful if it is not mapped to a database.  The snipet associates the ADMIN and USER logins to the database with default schemas.  This means that a simple CREATE TABLE statement will be created in the [STAGE] schema for any admins.  This can be overridden by explicitly stating the schema name.<\/p>\n
-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing user.\r\nIF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BSA_ADMIN')\r\nDROP USER [BSA_ADMIN]\r\nGO\r\n\r\n-- Add new user.\r\nCREATE USER [BSA_ADMIN] FOR LOGIN [BSA_ADMIN] WITH DEFAULT_SCHEMA=[STAGE]\r\nGO\r\n\r\n-- Delete existing user.\r\nIF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BSA_USER')\r\nDROP USER [BSA_USER]\r\nGO\r\n\r\n-- Add new user.\r\nCREATE USER [BSA_USER] FOR LOGIN [BSA_USER] WITH DEFAULT_SCHEMA=[RECENT]\r\nGO<\/span><\/pre>\n<\/p>\n
Each SQL Server database comes with a set of predefined SERVER<\/a> and DATABASE<\/a> roles.  For this example, we are going to give  the ADMIN account db_owner rights and the USER account db_datareader \/ db_datawriter access.<\/p>\n
-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Add to database owner role \r\nEXEC sp_addrolemember 'db_owner', 'BSA_ADMIN'\r\nGO\r\n\r\n-- Add to database read \/ write roles\r\nEXEC sp_addrolemember 'db_datareader', 'BSA_USER'\r\nEXEC sp_addrolemember 'db_datawriter', 'BSA_USER'\r\nGO<\/span><\/pre>\n<\/p>\n
I did not cover ALTER LOGIN which allows a DBA to reset passwords, force password changes, enable\/disable logins, and change the default database.  Also, the ALTER USER is useful for renaming an USER or changing the default schema.<\/p>\n
The key points to remember from this article is that a person needs a login account to connect to the server and a user account to access the database.  Each user account can be given privledges at the the table level or be assigned to a group like db_datareader.  Please use the provided links to find out more about Server and Database roles.  <\/p>\n
I will continue this business solution next time by talking about creating tables.  This talk will include a brief review of normal forms and go into depth of how to define fields.<\/p>\n","protected":false},"excerpt":{"rendered":"
The CREATE LOGIN and CREATE USER statements are part of the Data Definition Language (DDL) defined by Codd. Basically, a server login has to be defined so that person can connect to the server and a user login has to be created to map the server login to a particular database. I am going to continue my talk with the BSA hypothetical business problem from Part 1. Server logins allow access at the server level which can be defined using Windows Authentication or a SQL Server Authentication. Windows Authentication assumes…<\/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":[57,61,56,59,31,58,60,12,15,28,29],"class_list":["post-656","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-login","tag-alter-user","tag-create-login","tag-create-user","tag-database-developer","tag-drop-login","tag-drop-user","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/656","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=656"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/656\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}