[!NOTE] This lesson relies on objects created in Lesson 1 - Create database objects. Complete Lesson 1 before continuing on to lesson 2.
To complete this tutorial, you need SQL Server Management Studio and access to a SQL Server instance.
If you don’t have access to a SQL Server instance, select your platform from the following links. If you choose SQL Authentication, use your SQL Server login credentials. - Windows: Download SQL Server 2017 Developer Edition. - macOS: Download SQL Server 2017 on Docker.
To access the Database Engine users require a login. The login can represent the user’s identity as a Windows account or as a member of a Windows group, or the login can be a Database Engine SQL Server login that exists only in Database Engine SQL Server SQL Server Whenever possible you should use Windows Authentication.
By default, administrators on your computer have full access to SQL Server For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer. To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server SQL Server
In a Query Editor window of SQL Server Management Studio type and execute the following code replacing computer_name
with the name of your computer. FROM WINDOWS
indicates that Windows will authenticate the user. The optional DEFAULT_DATABASE
argument connects Mary
to the TestData
database, unless her connection string indicates another database. This statement introduces the semicolon as an optional termination for a SQL Server Management Studio Transact\-SQL statement.
This authorizes a user name Mary
, authenticated by your computer, to access this instance of SQL Server If there is more than one instance of SQL Server SQL Server on the computer, you must create the login on each instance that Mary
must access.
> [!NOTE]
> Because Mary
is not a domain account, this user name can only be authenticated on this computer.
Mary now has access to this instance of SQL Server but does not have permission to access the databases. She does not even have access to her default database TestData until you authorize her as a database user.
To grant Mary access, switch to the TestData database, and then use the CREATE USER statement to map her login to a user named Mary.
Type and execute the following statements (replacing computer_name
with the name of your computer) to grant Mary
access to the TestData
database.
Now, Mary has access to both SQL Server and the TestData
database.
As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.
Execute the following statement to give Mary
the EXECUTE
permission for the pr_Names
stored procedure.
In this scenario, Mary can only access the Products table by using the stored procedure. If you want Mary to be able to execute a SELECT statement against the view, then you must also execute GRANT SELECT ON vw_Names TO Mary
. To remove access to database objects, use the REVOKE statement.
[!NOTE]
If the table, the view, and the stored procedure are not owned by the same schema, granting permissions becomes more complex.
You must have EXECUTE permission to execute a stored procedure. You must have SELECT, INSERT, UPDATE, and DELETE permissions to access and change data. The GRANT statement is also used for other permissions, such as permission to create tables.
The next article teaches you how to remove database objects you created in the other lessons.
Go to the next article to learn more: > [!div class=“nextstepaction”] >Next steps