DRAG DROP
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014
Instance, with multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to
view the database structures on the server, but you do not want him or her to be able to
make changes or see the data in the tables.
The new hire’s login credentials are as follows:
Login name: JFree
Password: Jx672$qse
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the
smallest number of steps. Develop the solution by selecting and arranging the required code
blocks in the correct order. You may not need all of the code blocks.
Answer: See the explanation.
Explanation:
Box 1:
Box 2:
Note:
* MUST_CHANGE
Applies to: SQL Server 2008 through SQL Server 2014.
Applies to SQL Server logins only. If this option is included, SQL Server prompts the user for
a new password the first time the new login is used.
* The VIEW DEFINITION permission lets a user see the metadata of the securable on which
the permission is granted. However, VIEW DEFINITION permission does not confer access
to the securable itself. For example, a user that is granted only VIEW DEFINITION
permission on a table can see metadata related to the table in the sys.objects catalog view.
However, without additional permissions such as SELECT or CONTROL, the user cannot
read data from the table.
Alter server row is not needed. I’m guessing an accident is it show for box 1 answer.
0
0
Why GRANT CONNECT ANY DATABASE to [jfree] not working on 2012?
create login [jfree] with password =’Admin123~’
must_change,check_expiration=on <–works
grant view any definition to [jfree] <–works
GRANT CONNECT ANY DATABASE TO [jfree] <-failed
0
0
Apparently GRANT CONNECT ANY DATABASE TO [user] should work on SQL 2014
0
0
Why is he to be added to server role security admin?
0
0
USE MASTER;
CREATE LOGIN [JFree] WITH PASSWORD = ‘Jx672$qse’
CHANGE ON LOGIN, CHECK_EXPIRATION = ON;
ALTER SERVER ROLE [securityadmin] ADD MEMBER [JFree];
GRANT VIEW ANY DEFINITION TO [JFree];
GRANT CONNECT ANY DATABASE TO [JFree];
0
0
ALTER SERVER ROLE [securityadmin] ADD MEMBER [JFree] is not needed to satisfy the requirements.
Here is all that is needed:
Create Login JFree with password = ‘Jx672$qse’
Must_Change , Check_Expiration = on;
GO
Grant Connect Any Database To [JFree];
GO
Grant View Any Definition To [JFree];
GO
0
0