C# .NET version of SharePoint 2010's Add-SPShellAdmin


When you need a user or group to have PowerShell access to a SharePoint content database, you simply run the PowerShell command "Add-SPShellAdmin -Database <TheDatabaseObject or GUID>". This does all the backend work, creating the necessary database roles and schemas, and adding the users/groups to them. There may be times when using PowerShell is not as convenient. For example, if you have .NET custom code that creates content databases, you will need to write code to handle doing what Add-SPShellAdmin does if you want the process to be automated. If you don't, you will find that your admins do not have permissions to access the new database through PowerShell. The below code will handle it for you:

SqlConnection connection = null;

//Build SQL Query

StringBuilder sqlQueryBuilder = null;

try

{

//Add Shell Admin to new DB (add to db_owner role and SharePoint_Shell_Access role

String[] accountsToPermissionForShellAdmin = (<Your comma delimited list of users/groups you want to add>)

connection = new SqlConnection(<Your new database connection string. If you have a SPContentDatabase object, you can call db.DatabaseConnectionString>);

connection.Open();

//Create the SharePoint_ShellAccess Schema

SqlCommand myCommand = new SqlCommand("CREATE SCHEMA [SharePoint_Shell_Access]", connection);

myCommand.ExecuteNonQuery();

//Create the SharePoint_ShellAccess Role

myCommand = new SqlCommand("CREATE ROLE [SharePoint_Shell_Access]", connection);

myCommand.ExecuteNonQuery();

//Have the new Role be the owner of the new Schema

myCommand = new SqlCommand("ALTER AUTHORIZATION ON SCHEMA::[SharePoint_Shell_Access] to [SharePoint_Shell_Access]", connection);

myCommand.ExecuteNonQuery();

foreach (String account in accountsToPermissionForShellAdmin)

{

try

{

//Add as db_owner

myCommand = connection.CreateCommand();

myCommand.CommandText = "sp_addrolemember";

myCommand.CommandType = System.Data.CommandType.StoredProcedure;

myCommand.Parameters.AddWithValue("@rolename", "db_owner");

myCommand.Parameters.AddWithValue("@membername", account);

myCommand.ExecuteNonQuery();

//Add as SharePoint_Shell_Access

myCommand = connection.CreateCommand();

myCommand.CommandText = "sp_addrolemember";

myCommand.CommandType = System.Data.CommandType.StoredProcedure;

myCommand.Parameters.AddWithValue("@rolename", "SharePoint_Shell_Access");

myCommand.Parameters.AddWithValue("@membername", account);

myCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

 #Log error message somewhere

}

}

}

catch (Exception ex)

{

 #Log error message somewhere

}

//Close connection

if ((connection != null) && (connection.State == System.Data.ConnectionState.Open))

{

connection.Close();

}

Comments (0)

Skip to main content