Share via

How to: Grant Users Access to SQL Server Modeling Services

  • Article

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This tutorial demonstrates how to grant users access to the SQL Server Modeling Services database. This includes granting permissions to users for specific operations on Modeling Services Folders.

Note that part of this tutorial depends on the Modeling Services Folders tutorial. This tutorial assigns permissions to the HumanResourcesApp Folder. For more information, see How to: Create and Use SQL Server Modeling Services Folders.

This tutorial uses SQL Server Studio and T-SQL scripts to perform these administrative tasks in the Modeling Services database.

To create a new SQL Server login and user

  1. On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.

  2. In the Connect to Server dialog, type the name of the Modeling Services server in the Server name combo box.

  3. Specify the Authentication properties, and then click the Connect button.

    Note

    Managing Modeling Services permissions requires the caller to belong to either the RepositoryAdministrator role or the SQL Server sysadmin role.

  4. On the toolbar, click the New Query button. A blank query window should open.

  5. In the blank query window, add the following T-SQL statements. These statements create a new SQL Server login for a Windows domain account. It also creates a new Modeling Services user, named domainuser, for this login.

    Note

    In the following script, change the DOMAINNAME\username name to a valid Windows domain account in your environment.

    use Repository
go

-- Add a domain account login to SQL Server.
if not exists(select * from sys.syslogins where name = 'DOMAINNAME\username')
create login [DOMAINNAME\username] from windows
go

-- Add this windows login as a user to the Repository database.
if not exists(select * from sys.sysusers where name = 'domainuser')
create user domainuser for login [DOMAINNAME\username];
go

  6. Press the F5 key to run the T-SQL query.

To grant the user read access to the Modeling Services database

  1. At this point, you have created a new Windows login and a new Modeling Services database user, domainuser. However, the new user requires additional permissions to access Modeling Services views, stored procedures, and other database objects.

    In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.

    -- Add the user to a RepositoryReader role.
exec sp_addrolemember N'RepositoryReader', N'domainuser'
go

  2. Press the F5 key to run the T-SQL query. The new user is now a member of the RepositoryReader role.

    Note

    Other available Modeling Services roles include RepositoryReaderWriter for read/write access and RepositoryAdministrator for administrative access.

To view the default Modeling Services Folder permissions for the user

  1. Updatable security views in the Modeling Services database further filter the results that a user sees based on Modeling Services Folder permissions. To examine the effect of these permissions, first log onto a separate machine using the Windows login associated with the domainuser user. Note that this could also be accomplished by launching SQL Server Management Studio from the command prompt with the runas utility.

    Note

    Do not close the original query window opened with your administrative credentials. This tutorial requires the use of this administrative query window to change permissions for the new user. For the remainder of this tutorial, the two windows are referred to as the “administrative query window” and the “domainuser query window”.

  2. On the domainuser machine, open SQL Server Management Studio. On the File menu, select New, and then click Database Engine Query

  3. In the Connect to Database Engine dialog, specify the name of the Modeling Services server in the Server name combo box.

  4. In the Authentication drop-down list, select Windows Authentication.

  5. Press the Connect button to open a query window.

  6. In the new domainuser query window, add the following T-SQL statements.

    use Repository
go
select * from [Repository.Item].[CurrentPermissions]

  7. Press the F5 key to run the T-SQL query. This should show that the domain user has the read operation granted for an Modeling Services Folder with an identifier of 1. This Folder is named Repository, and it is the default Folder that owns many Modeling Services database objects.

  8. In the domainuser query window, replace the text with the following T-SQL statement. This query uses the updatable security view, [Repository.Item].[Folders], to get a list of the Modeling Services Folders that this user has rights to view.

    select * from [Repository.Item].[Folders]

  9. Press the F5 key to run the T-SQL query. Although the underlying table [Repository.Item].[FoldersTable] has more than one Modeling Services Folder, the query of the [Repository.Item].[Folders] view returns only the Folders that the current user has permissions to view. In this case, there is only one row returned for the Repository Folder.

    Note

    Note that the user received read access to the Folder, Repository, by joining the RepositoryReader role. Any rows owned by this default Folder can be read by members of the RepositoryReader role without granting additional permissions. In the same way, any rows owned by this default Folder can be updated by members of the RepositoryReaderWriter role without granting additional permissions.

To grant the user read permissions on an Modeling Services Folder

  1. Although users have read access to the default Repository Folder, most users will need additional permissions to other Folders that secure Modeling Services data. In this tutorial, you will grant permissions to the domainuser user to read data that belongs to the HumanResourcesApp Folder. For more information about how to create this Folder, see How to: Create and Use SQL Server Modeling Services Folders. In the administrative query window, replace the existing text with the following T-SQL statements.

    -- Obtain the Id for the Applications Folder by name.
declare @folder as [Repository.Item].[FolderId]
set @folder = [Repository.Item].[PathsFolder](N'HumanResourcesApp')

-- Folder Resource Kind.
declare @target_resourceKind as [Repository.Item].[SecuredResourceKindId]
select @target_resourceKind = Id 
from [Repository.Item].[SecuredResourceKinds] 
where Name = N'https://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder'

-- Read Operation.
declare @target_operation as [Repository.Item].[SecuredOperationId]
select @target_operation = Id 
from [Repository.Item].[SecuredOperations] 
where Name = N'https://schemas.microsoft.com/Repository/2007/10/Operations/Read'

-- Call GrantPrincipalPermission passing in the new login.
-- The claim kind, SID, and the SID itself are 
-- generated by the stored procedure.
exec [Repository.Item].[GrantPrincipalPermission] 
@principal = 'domainuser', 
@resourceKind = @target_resourceKind,
@resource = @folder, 
@operation = @target_operation,
@mayGrantOrRevoke = 1
go

    Note

    Note that the possible operations to grant include read (“https://schemas.microsoft.com/Repository/2007/10/Operations/Read”) and update (“https://schemas.microsoft.com/Repository/2007/10/Operations/Update”).

  2. Press the F5 key to run the T-SQL query. This query first obtains the identifier for the HumanResourcesApp Folder. It then gets the [Repository.Item].[SecuredResourceKindId] for the Modeling Services Folder resource kind. It also gets an [Repository.Item].[SecuredOperationId] for the read operation. Each of these values is passed as a parameter to the [Repository.Item].[GrantPrincipalPermission] stored procedure. This grants the domainuser user the read operation on the HumanResourcesApp Folder.

  3. To see the effect of this permissions change, open a new query window with the Windows login associated with the domainuser user. Do this in the SQL Server Management Studio instance on the other machine. Make sure that you are logged onto that machine with the target Windows domain account.

    Note

    Do not use the domainuser query window open from the previous steps. Permissions are cached for each database connection. A new connection is required to observe the new permissions.

  4. In the new domainuser query window, add the following T-SQL statements. These are the same statements that were run previously.

    use Repository
go
-- View the user's current permissions.
select * from [Repository.Item].[CurrentPermissions]

-- View the Repository Folders visible for this user.
select * from [Repository.Item].[Folders]

  5. Press the F5 key to run the T-SQL query. Note that the first set of results shows that the user has Read access to the HumanResourcesApp Folder. The second set of results demonstrates the effect of this new permission. The query to the updatable security view, [Repository.Item].[Folders], returns the Modeling Services Folders that the user has permissions to see. It now lists both the default Repository Folder, the HumanResourcesApp Folder, and any subfolders of the HumanResourcesApp Folder.

    Note

    Note that Folder permissions are recursive. For example, if the HumanResourcesApp Folder had several layers of subfolders, permissions on the HumanResourcesApp Folder would grant permissions on each subfolder as well.

See Also

Tasks

How to: Remove User Permissions

Concepts

Security Tasks (Modeling Services)
SQL Server Modeling Services Folder Design Patterns