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'; 

	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"
	    $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 = $database.Users[$LoginName]
			if(-not $database.Roles.Contains($roleName)){
				$role = New-Object('Microsoft.SqlServer.Management.smo.DatabaseRole') $database, $roleName
			$role = $database.Roles[$roleName]
			$objDbPerms = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet 
			$objDbPerms.Execute = $true
			$database.Grant($objDbPerms, $roleName);
	$_ | fl -Force

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.