Edit

Share via

Create a database and permissions (SQL Server and RevoScaleR tutorial)

  • Article

Applies to: SQL Server 2016 (13.x) and later versions

This is tutorial 1 of the RevoScaleR tutorial series on how to use RevoScaleR functions with SQL Server.

This tutorial describes how to create a SQL Server database and set the permissions necessary for completing the other tutorials in this series. Use SQL Server Management Studio or another query editor to complete the following tasks:

  • Create a new database to store the data for training and scoring two R models
  • Create a database user login with permissions for creating and using database objects

Create the database

This tutorial requires a database for storing data and code. If you're not an administrator, ask your DBA to create the database and login for you. You'll need permissions to write and read data, and to run R scripts.

  1. In SQL Server Management Studio, connect to an R-enabled database instance.

  2. Right-click Databases, and select New database.

  3. Type a name for the new database: RevoDeepDive.

Create a login

  1. Click New Query, and change the database context to the master database.

  2. In the new Query window, run the following commands to create the user accounts and assign them to the database used for this tutorial. Be sure to change the database name if needed.

  3. To verify the login, select the new database, expand Security, and expand Users.

Windows user

 -- Create server user based on Windows account
USE master
GO
CREATE LOGIN [<DOMAIN>\<user_name>] FROM WINDOWS WITH DEFAULT_DATABASE=[RevoDeepDive]

 --Add the new user to tutorial database
USE [RevoDeepDive]
GO
CREATE USER [<user_name>] FOR LOGIN [<DOMAIN>\<user_name>] WITH DEFAULT_SCHEMA=[db_datareader]

SQL login

-- Create new SQL login
USE master
GO
CREATE LOGIN [DDUser01] WITH PASSWORD='<type password here>', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

-- Add the new SQL login to tutorial database
USE RevoDeepDive
GO
CREATE USER [DDUser01] FOR LOGIN [DDUser01] WITH DEFAULT_SCHEMA=[db_datareader]

Assign permissions

This tutorial demonstrates R script and DDL operations, including creating and deleting tables and stored procedures, and running R script in an external process on SQL Server. In this step, assign permssions to allow these tasks.

This example assumes a SQL login (DDUser01), but if you created a Windows login, use that instead.

USE RevoDeepDive
GO

EXEC sp_addrolemember 'db_owner', 'DDUser01'
GRANT EXECUTE ANY EXTERNAL SCRIPT TO DDUser01
GO

Troubleshoot connections

This section lists some common issues that you might run across in the course of setting up the database.

  • How can I verify database connectivity and check SQL queries?

    Before you run R code using the server, you might want to check that the database can be reached from your R development environment. Both Server Explorer in Visual Studio and SQL Server Management Studio are free tools with powerful database connectivity and management features.

    If you don't want to install additional database management tools, you can create a test connection to the SQL Server instance by using the ODBC Data Source Administrator in Control Panel. If the database is configured correctly and you enter the correct user name and password, you should be able to see the database you just created and select it as your default database.

    Common reasons for connection failures include remote connections are not enabled for the server, and Named Pipes protocol is not enabled. You can find more troubleshooting tips in this article: Troubleshoot Connecting to the SQL Server Database Engine.

  • My table name has datareader prefixed to it - why?

    When you specify the default schema for this user as db_datareader, all tables and other new objects created by this user are prefixed with the schema name. A schema is like a folder that you can add to a database to organize objects. The schema also defines a user's privileges within the database.

    When the schema is associated with one particular user name, the user is the schema owner. When you create an object, you always create it in your own schema, unless you specifically ask it to be created in another schema.

    For example, if you create a table with the name TestData, and your default schema is db_datareader, the table is created with the name <database_name>.db_datareader.TestData.

    For this reason, a database can contain multiple tables with the same names, as long as the tables belong to different schemas.

    If you are looking for a table and do not specify a schema, the database server looks for a schema that you own. Therefore, there is no need to specify the schema name when accessing tables in a schema associated with your login.

  • I don't have DDL privileges. Can I still run the tutorial??

    Yes, but you should ask someone to pre-load the data into the SQL Server tables, and skip ahead to the next tutorial. The functions that require DDL privileges are called out in the tutorial wherever possible.

    Also, ask your administrator to grant you the permission, EXECUTE ANY EXTERNAL SCRIPT. It is needed for R script execution, whether remote or by using sp_execute_external_script.

Next steps

Create SQL Server data objects using RxSqlServerData