Add a Database Role and Grant EXEC

Quick post on how to add a database role to all your databases and grant EXEC to said role.  This will also add the specified user to said role (aptly named db_exec).  Use with caution, minimally tested.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
 
$servers = @('ServerName')
$roleName = 'db_exec'
$LoginName = 'LoginName'; 

 
 try{
	foreach($server in $servers){
	 
	    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	 
	    if (-not $srv.Logins.Contains($LoginName)){ 
	       throw "Login $LoginName does not exist on server $server"
		   return;
		}
		
	    $login = $srv.Logins[$LoginName]
	 
	    foreach($database in $srv.Databases | where{-not $_.IsSystemobject -and $ignoreDBs -notcontains $_.Name -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal}){
		
			if(-not $database.Users.Contains($login.Name)){
				$user = New-Object('Microsoft.SqlServer.Management.Smo.User') $database, $login.Name
				$user.Login = $login.Name
	        	$user.create();
			}
			
			$user = $database.Users[$LoginName]
			
			if(-not $database.Roles.Contains($roleName)){
				$role = New-Object('Microsoft.SqlServer.Management.smo.DatabaseRole') $database, $roleName
				$role.Create();
			}
			
			$role = $database.Roles[$roleName]
			$role.AddMember($user.Name);
			
			$objDbPerms = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet 
			$objDbPerms.Execute = $true
			$database.Grant($objDbPerms, $roleName);
			$database.Alter();
	    }
	}
}
catch{
	$_ | fl -Force
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: