CODEWITHSUNDEEP
sql-serverreporting-services
Kevin
Kevin

Reputation: 847

SSRS call to stored procedure fails, Cannot find user 'dbo'

I've got a SSRS report that is calling a stored procedure in my database.

Locally everything works, I am connecting with Windows authentication locally to hit the database and there are no problems.

When the SSRS report is deployed it's set up to use a different login, and this login has the correct role to grant execute on stored procedures. However, when trying to view the report on SSRS server I get this message:

Cannot find the user 'dbo', because it does not exist or you do not have permission.

If I remove the role that allows the grant execute rights I get a more specific error saying that I don't have permission to execute the stored procedure. But once this role is put back on the user account SSRS runs as it goes back go showing an error

Cannot find the user 'dbo'

The role I have that is granted execute rights is granted by 'dbo' user, this is the database owner. I have found things online saying that it's possible that a database backup messed up some user logins. I have checked, and the database owner sid is the same as the sysuser sid for name = 'dbo', so I do not think this is the problem.

Does anyone else know what I might be able to try?

EDIT: Details on stored procedure: it is a simple select with some inner joins. At the end of the stored procedure is the following, granting access to the role my SSRS user is part of:

GRANT EXECUTE
    ON OBJECT::[dbo].[Application_LoadData] TO [SSRSUserRole]
    AS [dbo];

Upvotes: 3

Views: 1143

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

A stored procedure includes all statements in the batch after the CREATE PROCEDURE. So a proc like this

CREATE PROCEDURE USP_FOO
AS
BEGIN
  SELECT * FROM FOO
END

GRANT EXECUTE ON USP_FOO TO SOMEUSER AS DBO;

Will attempt to execute to GRANT every time the procedure is run, and will fail when not run by dbo.

The script to create the procedure should have a batch separator before the grant. eg:

CREATE PROCEDURE USP_FOO
AS
BEGIN
  SELECT * FROM FOO
END

GO

GRANT EXECUTE ON USP_FOO TO SOMEUSER AS DBO;

Upvotes: 7

Related Questions