Assignment
SQL Server 2012 User Admin Tutorial
To Create User
There are two steps to create a user in SQL Server: create a login first, and then create a user. A valid login name is required to connect to an SQL Server instance. A login could be either of below.
A Windows login that has been granted access to SQL Server (username/password is kept by local OS or domain controller)
An SQL Server login, that is maintained within SQL Server (username/password is kept by SQL Server)
A login can have associated users in different databases, but only one user per database.
SQL Server and Windows Authentication Mode
CREATE LOGIN steve WITH PASSWORD='abc123';
CREATE USER steve FOR LOGIN [steve];
Windows Authentication Mode
CREATE LOGIN [cse1\steve] FROM WINDOWS;
CREATE USER steve FOR LOGIN [cse1\steve];
To allow user to connect to MS SQL Database
GRANT CONNECT TO steve;
To Remove User
To remove a user, use DROP. Users that own securables cannot be dropped from the database.
DROP USER steve;
A user can be disabled by revoking its CONNECT permission by executing
REVOKE CONNECT FROM steve;
Additional Reading
Managing Logins, Users, and Schemas How-to Topics https://msdn.microsoft.com/en-us/library/aa337552.aspx (Create schemas, create a login and create a database user)
Deliverables
Note: 1) Avoid using stored system procedure wherever possible. Example: use CREATE LOGIN instead of sp_addlogin see https://msdn.microsoft.com/en-us/library/ms173768.aspx
2) DO NOT USE GUI. You will not receive points if you use GUI.
D1. Create SQL Server login user1 - include the SQL script
D2. Set default database for user1 to AdventureWorks - include the SQL script
D3. Create user for login user1 in database AdventureWorks - include the SQL script
D4. Add permissions (see slides) for user1 to read and create tables in AdventureWorks and Insert rows. - include the SQL script and check user1's properties and provide a screenshots similar to one below.
D5. Perform following steps and answer questions - just include your answer in the deliverable.
Change type of authentication for MS SQL Server to Windows and SQL Server.
Right-click the Server and choose Properties. Under Security pick SQL Server and Windows Authentication mode
b) Restart MS SQL Server (not the computer) see below.
Disconnect and connect using SQL Server Authentication. Connect to SQL Server as user1. Try to select all products from Production.Product table (AdventureWorks database). What does the query return? How many rows were returned?
D6. As user1 create new table user1_table. - include the SQL code for creating and populating table.
testID
1234
D7. Disconnect and connect again using Windows Authentication Mode. Disable user1 login.- include the SQL script. Log off and logon using user1 login. - Describe what happened in the deliverable
D8. Logon using Windows Authentication Mode delete user1 SQL Server login.- include the SQL code used and explain what happened with table created in D6, user1_table.
D9. Use who2 procedure to find information about your own login. - include a screen shot.
Check system stored procedure in master database. Run who system procedure. You can find information about current Microsoft SQL Server users and processes. System Stored Procedures can be found under Programmability -> Storage Procedures
To run procedure you need to use following query:
execute procedure_schema.procedure_name
D10. Go to computer management and create user1 with password abc123. Grant user1 permission to login to SQL Server (It is Windows Integrated authentication) - include the SQL script for granting permission in the deliverable.
D11. Write a script to add user user1 to database AdventureWorks. Delete user user1 and login user1 - include the SQL script in the deliverable.