Create a new user called cis605_usr. Use Master. assign a password of ‘abcd’, set check_policy to off and check_expiration to off (Why set these two to off?). Execute the sp_addsrvrolemember to add the server login name to cis605_usr and the server role name to sysadmin.

To verify that the user was created click on security/Logins in the Object Explorer as shown below.

*****

2. Grant database cis605 access to cis605_usr using an sp procedure.

To verify that the user was created click on security/Users for database cis605 as shown below.

*****

3. Grant create table, procedure, select to cis605_usr for database cis605.

Add the with grant option to create table for cis605_usr.

*****

4. Grant all defined permissions for database cis605 to cis605_usr.

*****

5. Write a query to display the name, createdate, dbname, has access, and loginname columns using syslogins and matching on cis605_usr created above.
 
 

T BUDSTUTTETEMP\MSS2012XE (SQL Server 11.0.3153 - BELLEVUE\stutte) Databases Security Logins ##MS_PolicyEventProcessingLogin## &##MS_PolicyTsqlExecutionLogin## BELLEVUE\Stutte BUILTIN\Users cis605_usr MyNewAdminUser NT AUTHORITY\SYSTEM NT Service\MSSQLSMSS2012XE NT SERVICE\SQLWriter NT SERVICE\Winmgmt & sa tom Server Roles +