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 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.
The snipet below creates two logins using SQL Server Authentication.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<span style="color: #008000;">-- Which database to use. USE [master] GO -- Delete existing login. IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BSA_ADMIN') DROP LOGIN [BSA_ADMIN] GO -- Add new login. CREATE LOGIN [BSA_ADMIN] WITH PASSWORD=N'M0a2r0c9h11$', DEFAULT_DATABASE=[BSA] GO -- Delete existing login. IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BSA_USER') DROP LOGIN [BSA_USER] GO -- Add new login. CREATE LOGIN [BSA_USER] WITH PASSWORD=N'M0a2r0c9h11#', DEFAULT_DATABASE=[BSA] GO</span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<span style="color: #008000;">-- Which database to use. USE [BSA] GO -- Delete existing user. IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BSA_ADMIN') DROP USER [BSA_ADMIN] GO -- Add new user. CREATE USER [BSA_ADMIN] FOR LOGIN [BSA_ADMIN] WITH DEFAULT_SCHEMA=[STAGE] GO -- Delete existing user. IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BSA_USER') DROP USER [BSA_USER] GO -- Add new user. CREATE USER [BSA_USER] FOR LOGIN [BSA_USER] WITH DEFAULT_SCHEMA=[RECENT] GO</span> |
Each SQL Server database comes with a set of predefined SERVER and DATABASE roles. For this example, we are going to give the ADMIN account db_owner rights and the USER account db_datareader / db_datawriter access.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<span style="color: #008000;">-- Which database to use. USE [BSA] GO -- Add to database owner role EXEC sp_addrolemember 'db_owner', 'BSA_ADMIN' GO -- Add to database read / write roles EXEC sp_addrolemember 'db_datareader', 'BSA_USER' EXEC sp_addrolemember 'db_datawriter', 'BSA_USER' GO</span> |
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.
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.
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.
I don’t even know how I ended up here, but I thought this post was good. I do not know who you are but certainly you’re going to a famous blogger if you are not already ;) Cheers!
I wish to get across my gratitude for your kind-heartedness supporting men and women who really need guidance on this one area of interest. Your very own dedication to passing the solution around had been unbelievably good and have always encouraged girls just like me to realize their aims. Your personal warm and friendly facts implies a great deal to me and far more to my fellow workers. Best wishes; from all of us.