Creating Role in SQL Server Database using C#


 

To handle SQL server Database using .net, you will have to use the .net library
Microsoft.AnalysisServices  

using Microsoft.AnalysisServices; 

For this you will need to add reference to the dll Analysis Management Objects.
This can be found in the .NET tab of the add reference window. Of course, this will give you the
facility to handle Analysis Services Database. Similarly way can be found for Database Engine 
also.  

The below code shows how to create Role in the Analysis Services Database using C#. The steps can
be defined as below:  
1. Create a Server Object 
2. Connect to the Server with the desired Connection String 
3. Get the desired Database. 
4. Create a Role with a specified Role name. 
5. Create a Database Permission object. 
6. Assign the desired permission and Role. 
7. Add member to the role. 
8. Update the permission object. 
9. Update the Role object. 
10. Update the Database.

 

public class RoleCreator
    {
        private Database db;

        private static object ConnectAnalysisServices(string strDBServerName, string strProviderName, 
            string catalogName)
        {
            try
            {
                Server objServer = new Server();
                string strConnection = "Data Source=" + strDBServerName + ";Provider=" + strProviderName + 
                                        ";Initial Catalog=" + catalogName + ";";
                //Disconnect from current connection if it's currently connected.
                if (objServer.Connected)
                    objServer.Disconnect();
                
                objServer.Connect(strConnection);

                return objServer;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public Role GetRole(string roleName)
        {
            Role role;
            if (!db.Roles.Contains(roleName))
            {
                role = db.Roles.Add(db.Roles.GetNewName("NewRole"));
            }
            else
            {
                //Role already exists with the same name
                role = db.Roles.GetByName(roleName);
            }
            return role;
        }

        public void AssignPermission(string roleName)
        {
            string permissionName = "ReadPermissionForNewRole";
            DatabasePermission dbPermission;
            if (!db.DatabasePermissions.Contains(permissionName))
            {
                dbPermission = db.DatabasePermissions.Add(db.DatabasePermissions.GetNewName(permissionName));
            }
            else
            {
                dbPermission = db.DatabasePermissions.GetByName(permissionName);
            }

            dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed;
            //dbPermission.Read = ReadAccess.Allowed;
            //dbPermission.Write = WriteAccess.Allowed;
            //dbPermission.Process = true;
            //dbPermission.Administer = true;

            dbPermission.RoleID = roleName;

            dbPermission.Update();
        }

        public void AssignMember(Role role, string memberName)
        {
            role.Members.Add(new RoleMember(memberName));
        }

        public void DriverMethod()
        {
            Server server = (Server)ConnectAnalysisServices(@"BDDH01-W0198\MSSQLSERVER2008", "MSOLAP", "AZ_2005");
            db = server.Databases["AZ_2005"];

            string roleName = "NewRole";
            Role role = GetRole(roleName);
            AssignPermission(roleName);
            AssignMember(role, "Guest");

            //updating the role and database is very important. 
            //Otherwise you will not get the effect in the database.
            role.Update();
            db.Update();
        }
    }