How to: Remove User Permissions

[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 remove user access from the SQL Server Modeling Services database. This includes removing permissions from users for specific operations on Modeling Services Folders. For more information about creating Modeling Services Folders, see How to: Create and Use SQL Server Modeling Services Folders.

Note

This tutorial removes permissions from a Windows domain login associated with a Modeling Services database user named domainuser. Another tutorial explains how to create this user and add Modeling Services database and Folder permissions. For more information, see How to: Grant Users Access to SQL Server Modeling Services.

Note

The processes for granting and revoking Modeling Services roles and Folder permissions can be used together to make security changes. For example, an administrator can remove a user’s role membership for RepositoryAdministrator and then add them to the role for RepositoryReaderWriter.

To revoke Modeling Services Folder permissions

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 user permissions in the Modeling Services database 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 query window, add the following T-SQL statements to revoke the read permissions on the Applications Folder for the user named domainuser.

   use Repository
   go
   
   -- 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'
   
   -- GrantPrincipalPermission passing in the new login.
   -- The claim kind, SID, and the SID itself are 
   -- generated by the stored procedure.
   exec [Repository.Item].[RevokePrincipalPermission] 
   @principal = 'domainuser', 
   @resourceKind = @target_resourceKind,
   @resource = @folder,
   @operation = @target_operation
   go
   

6. Press the F5 key to run the T-SQL query. This query first obtains the identifier for the Applications 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].[RevokePrincipalPermission] stored procedure. This revokes the permissions for the domainuser user to read rows that belong to the HumanResourcesApp Folder.

To remove user access to the Modeling Services database

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

   use Repository
   go
   
   exec sp_droprolemember @rolename = 'RepositoryReader' , @membername = 'domainuser'
   exec sp_droprolemember @rolename = 'RepositoryReaderWriter' , @membername = 'domainuser'
   exec sp_droprolemember @rolename = 'RepositoryAdministrator' , @membername = 'domainuser'
   go
   

2. Press the F5 key to run the T-SQL query. This removes the database user domainuser from all possible standard Modeling Services user roles.

   Note

   Note that an administrator can grant database object permissions to a user outside of these roles. Any custom user permissions must be removed as well. One way to do this is with the T-SQL REVOKE statement.

3. To completely restrict the user from connecting to the Modeling Services database, remove the user from the database with the DROP USER T-SQL statement. In the query window, replace the existing text with the following statement.

   -- Drop the domain user from the Repository database.
   drop user domainuser
   

4. To completely restrict the user from connecting to the SQL Server, remove the login from the server with the DROP LOGIN T-SQL statement. In the query window, replace the existing text with the following statement.

   Note

   In the following script, change the DOMAINNAME\username name to the window domain account that was previously associated with the domainuser user.

   -- Drop the login on the SQL Server.
   drop login [DOMAINNAME\username]
   

See Also

Tasks

How to: Grant Users Access to SQL Server Modeling Services

Concepts

Security Tasks (Modeling Services)