You plan to use Microsoft Azure SQL Database instances with strict user access control. A user object must:
-> Move with the database if it is run elsewhere
-> Be able to create additional users
You need to create the user object with correct permissions.
Which two Transact-SQL commands should you run? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Click on the arrows to vote for the correct answer
A. B. C. D. E.CD
C: ALTER ROLE adds or removes members to or from a database role, or changes the name of a user-defined database role.
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL
Server.
D: CREATE USER adds a user to the current database.
Note: Logins are created at the server level, while users are created at the database level. In other words, a login allows you to connect to the SQL Server service
(also called an instance), and permissions inside the database are granted to the database users, not the logins. The logins will be assigned to server roles (for example, serveradmin) and the database users will be assigned to roles within that database (eg. db_datareader, db_bckupoperator).
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sqlTo create a user object with the correct permissions in Microsoft Azure SQL Database instances, we need to run the CREATE LOGIN and CREATE USER commands in Transact-SQL. The correct answers are B and D.
Here's a detailed explanation of each command:
A. ALTER LOGIN Mary WITH PASSWORD = 'strong_password'; This command changes the password of an existing login. It is not relevant to creating a new user object with the ability to move with the database and create additional users.
B. CREATE LOGIN Mary WITH PASSWORD = 'strong_password'; This command creates a new login with the specified password. A login provides access to a SQL Server instance, but it is not directly associated with any database. However, if you create a user in a database and map it to this login, the user will be able to connect to the instance using this login.
C. ALTER ROLE db_owner ADD MEMBER Mary; This command adds an existing login or user to the db_owner role, which is a built-in database role that provides full permissions to a database. It is not relevant to creating a new user object with the ability to move with the database and create additional users.
D. CREATE USER Mary WITH PASSWORD = 'strong_password'; This command creates a new user in the current database and associates it with a login. If the login does not exist, it will be created implicitly. The user can then be granted permissions to access the database objects. This command satisfies the requirement to create a user object with the ability to move with the database and create additional users.
E. GRANT ALTER ANY USER TO Mary; This command grants the ability to alter any user to an existing login or user. It is not relevant to creating a new user object with the ability to move with the database and create additional users.
Therefore, the correct answers are B and D. We need to create a login and a user with the CREATE LOGIN and CREATE USER commands, respectively. For example:
sqlCREATE LOGIN Mary WITH PASSWORD = 'strong_password'; CREATE USER Mary WITH PASSWORD = 'strong_password'; After creating the user, you can grant it permissions to access the database objects using the appropriate Transact-SQL commands.