Run as Drop-down is Empty in Job Step Properties

Note: This article assumes that you have already created a Credential and a Proxy mapped to that credential. You have also created a SQL Server Agent Job and you're trying to assign a proxy to a step within that job.

I have read many questions posted on various websites on how to assign a proxy to a particular job. Some of them have described that their "Run as" drop down is empty.




This usually means that they haven't created a proxy. But sometimes, your DBA have created the proxy account, but forgot to grant your user the permission to that proxy account. But unless you have sysadmin role on your account, the proxy will not magically appear on your drop-down. You need to explicitly add the principals that will have access to that proxy. To do that, the sysadmin must use SSMS to go to Object Explorer -> Database Server -> SQL Server Agent -> Proxies -> Subsystem -> double-click the proxy account to open the Proxy Account Properties dialog, click on the Principals page. The right side of the dialog will look like the following.




Click on the Add button to find the SQL Login or role for your user that you want to give permission to this proxy. Once this has been done, the proxy should appear on your Run as drop-down.

The EXECUTE permission was denied on the object, 'MyStoredProcedure', database 'MyDatabase', schema 'dbo'.

When you first create a login in SQL Server 2008, you can grant read, write, or ddl permissions by assigning the roles db_datareader, db_datawriter, and db_ddladmin, respectively. But by default, no specific role exists for giving the user execute permission. The easiest way to grant execute permission is by opening a SQLQuery window for that database, and running "grant execute to <user>", where user is the name of the user login.

For the command-line challenged, you can go to the database properties by right clicking the database name on the Object Explorer, select Properties to open the Database Properties dialog. On the left, select the Permissions page, under the Explicit tab, scroll down until you see the Execute permission, and check the Grant column like so.



This gives the user the ability to run any stored procedure in that database, including any stored procedure created afterwards.

Granting Permissions to User Not Working in SQL Server

I recently investigated an issue where a domain user was "suddenly" unable to perform select queries on any table in a database. This was after verifying that the user had the correct permissions from within Management Studio under Security -> Logins. The most apparent evidence that it was a permissions issue is that when the user was given the sysadmin role, he regains the ability to do his queries. Of course, granting the sysadmin role is not the proper solution.

It turns out that recently, the users were migrated to a new Active Directory group. This new group was given the correct permissions in the database, while the old group was "deactivated" by adding the db_denydatareader and db_denydatawriter role memberships. Unfortunately for this user, he was added to the new group, but not removed from the old group, and since db_denydatareader takes precedence over db_datareader, the poor user was locked out of the database. So the lesson is, aside from checking to make sure that the user has the correct permissions, make sure that all login groups that the user is a member of also has the correct permissions. It only takes one db_denydatareader role to override all the  db_datareader roles granted.

Migrating SQL Server Analysis Services From 2000 to 2008

If you use Google to find how to migrate your cube definitions from AS 2000 to AS 2008, you'll probably find the following link from MSDN:

http://msdn.microsoft.com/en-us/library/ms143278%28v=sql.105%29.aspx

If you follow the instructions, you will probably encounter the following error:

Source server: The Migration Wizard cannot continue because the DSO client is not installed on this computer. Close the current Migration Wizard, install the DSO client on the computer, and then try running the Migration Wizard again.



The problem is that it tells you to use a tool that does not come with a vanilla install of SQL Server 2008. In fact, it takes a little digging to find this mysterious DSO client. The reason for this is that the component that lets you migrate from AS 2000 to 2008 was made for SQL Server 2005, and they did not upgrade it for SQL Server 2008. The good news is that even though it was made for SQL Server 2005, it works with SQL Server 2008 anyway. Go to this link:

http://www.microsoft.com/download/en/details.aspx?id=15748

Scroll down to find the Microsoft SQL Server 2005 Backward Compatibility Components and download the appropriate version for your platform. This should solve the missing DSO client error from the Migration Wizard.

Could not load file or assembly 'System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies.

I recently migrated a SQL Server 2000 Analysis Services database to SQL Server 2008 using the Migration Wizard. But when I tried to process the cubes on the migrated database, I was getting the following error:

Could not load file or assembly 'System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies.


The weird thing is that when I test my data sources and data views, all the connections test without problems. I also checked my GAC, and I can plainly see that System.Transactions is installed.

After googling the error, I found that many people have encountered this error for various reasons. I tried the solutions that had worked for other people to no avail. I finally decided to uninstall the Microsoft SQL Server 2005 Backward Compatibility Package, and lo and behold, I'm now able to process all my cubes.