Which Transact-SQL statement should you use?
You are responsible for managing an instance of SQL Server 2008 named SQL1 . SQL1 contains a schemanamed HumanResource.
You want to transfer the ownership of the schema to a user named HRHead . Which Transact-SQL statement should you use?
Which statement should you use to create the trigger?
You are the database administrator for your company and manage all the SQL Server 2008 databases of the company.
You are responsible for performing the daily backup activities on all the databases on the server. Database users regularly create new objects in one of the databases.
You want to be notified when new objectsare created in the database. You want to create a trigger that will fire whenever a new user-defined function is created in the database.
The trigger should insert statement-specific data, such as the name of the database user who issued the CREATE FUNCTION statement, the time the statement was issued, and the Transact-SQL statement that was issued, intoa database table named Log_structures . The Log_structures table was created by using the followingstatement:
CREATE TABLE Log_structures (
user1 nvarchar(100),
createtime datetime,
SQL_stat nvarchar(2000));
Which statement should you use to create the trigger?
What should you do?
You are the SQL administrator for your company. You manage all of the SQL Server 2008 computers for yourcompany. All of your databases use Windows Authentication.
Because of recent security issues with human resources information, you have been asked to grant a user inhuman resources the right to remove access for Windows logins to the HR database. You do not want to grantthe user more permissions than required. What should you do?
Which system stored procedure should you use to configure the MaintenanceTasks_sp stored procedure?
You are responsible for managing an instance of SQL Server 2008. You create a stored procedure named MaintenanceTasks_sp to perform maintenance tasks in the tempdb database. You want to ensure that the MaintenanceTasks_sp stored procedure executes automatically when the SQL Server starts.
Which system stored procedure should you use to configure the MaintenanceTasks_sp stored procedure?
What should you do to allow the members of the Role_Admin role to update the Accounts table with the leastadmi
You are the database administrator for a banking firm and maintain all the SQL Server 2008 databases of thefirm.
You create the Accounts table in the Sales schema of the database to store all information regarding thefirm’s customers.
To update the account details of a customer’s account, you instruct another databaseadministrator, Michelle, to create a stored procedure named Upd_account_details in the Sales schema in thedatabase. Michelle runs the following script to create the Upd_account_details procedure:
CREATE PROC Upd_account_details
@AccountName nvarchar(40)
@Address1 nvarchar(40)
@Address2 nvarchar(40)
@City nvarchar(25)
@State nvarchar(2)
@Phone nvarchar(20) AS
INSERT INTO Accounts VALUES
(@AccountName, @Address1, @Address2, @City, @State, @Phone) GO
ALTER AUTHORIZATION ON OBJECT::Upd_account_details TO Michelle; GO
You want to assign permissions on this procedure to the members of the Role_Admin role. You issue the following statement:
GRANT ALL ON OBJECT::Sales.Upd_account_details to Role_Admin
When John, a member of the Role_Admin role, attempts to update a record with the Upd_account_details stored procedure, he receives an error and is unable to update the record.
What should you do to allow the members of the Role_Admin role to update the Accounts table with the leastadministrative effort?
What should you do?
You are a database administrator in your company. You maintain the Corpdb database on the Sql1 SQL Server 2008 computer.
The database contains data that is specific to the corporate department of the company and isaccessed only by corporate department members.
Due to an organizational restructuring, the corporatedepartment has been relocated.
You are required to transfer the Corpdb database to a SQL Server 2008 instance named Sql2 installed at the new location.
You successfully transfer the database to Sql2 by using SSIS packages.
The managers of the corporatedepartment complain they cannot log in to Sql2 .
Although you can log in, you receive a Select permissiondenied error after executing Transact-SQL queries on the database tables.
You want to ensure that managers inthe corporate department are able to successfully access the Corpdb database and configure security for theusers in the corporate department. What should you do?
which role should you add John’s account?
You are the SQL administrator for your company. You have two SQL Server 2008 computers named SQL_corp and SQL_public.
Multiple databases reside on these two servers.
A new user named John has been hired in your department.
John must be able manage permissions for the SQL_corp server.
He should also be able to manage permissions on the Sales database on the SQL_public computer.
You should not grant John any permissions he does not need. To which role should you add John’s account?
What should you do to achieve this?
You are the database administrator of your company. You configure automatic auditing by using SQL ServerAudit on a server that runs an instance of SQL Server 2008.
You want to ensure that the failed login attempts tothe instance are logged in the Windows Security event log. What should you do to achieve this?
Which SQL Server entity should you use?
You are the database administrator of your company. You create a logon trigger on a SQL Server 2008 computer.
The trigger will be used to deny login attempts to the server that are initiated by a login named Admin_login ifthere are already two existing sessions for that login.
You want to view the metadata for the logon trigger youcreated. Which SQL Server entity should you use?
Which Transact-SQL statement should you use first?
You are your company’s SQL administrator. A SQL Server 2008 computer named SQL_main contains adatabase named Finance.
The Finance database is used by the accounting department to manage yourcompany’s accounts payable and accounts receivable.
All users use SQL logins to access this database.Password expiration is not enabled by default.
A user named Amy in the accounting department took a medical leave of absence and has just returned.
Duringthat time, her SQL login, Amy16 , was disabled. She has forgotten her password. She contacts you requestingthat you change her password.
However, you must ensure that your password change is only temporary and thatshe resets the password at login.
Which Transact-SQL statement should you use first?