Problem while execution of stored procedure

Question

CREATE LOGIN [NEWUSER] WITH PASSWORD=N'XXXX', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

IF DATABASE_PRINCIPAL_ID('NEWROLE') IS NULL
BEGIN
        CREATE ROLE NEWROLE AUTHORIZATION dbo
        PRINT 'MESSAGE: Role [NEWROLE] created on the database.'
END

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NEWUSER' AND type = N'S')
BEGIN
        CREATE USER [NEWUSER] FOR LOGIN [NEWUSER] WITH DEFAULT_SCHEMA=[NEWUSER]
        EXEC sp_addrolemember N'NEWROLE', N'NEWUSER'
        PRINT 'MESSAGE: Login [NEWUSER] created on the database.'
END

GRANT EXECUTE ON [dbo].[GetSPROC] TO NEWROLE

But while executing the stored procedure by connecting with NEWUSER, its throwing error

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

Can any one please help me on this issue.

Answer 1

Hi Satish,

the error message shown can be happen for two reasons

1)You are using the wrong database to call the stored procedure

2) You are not having suffiecient permission to run the stored procedure

The account that you are using when calling the stored procedure must not be the same account that you are using to check it. Make sure that the account that you are using to execute the stored procedure has access to the object.

or

use APPROPRIATE_DB

Answer 2

Do you run the script under master database? Is that stored procedure located in master database as well?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Script will be running against the user db. ( not the master db) Stored procedure is located in the user db only.

I am connecting to management studio using the newuser  and executing the stored procedure.

Answer 4

I see DEFAULT_DATABASE=[master] so you need to change a scope of the database to run the query

USE dbname 

GO

GRANT EXECUTE ON [dbo].[GetSPROC] TO NEWROLE

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

now I connected with sa/password and trying to run the following script. ( tried with windows authentication also)

USE <dbname>

GO

GRANT EXECUTE ON [dbo].[GetSPROC] TO NEWROLE

( here I changed role to user also, still giving the similar error)

Following error is occurred.

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

Answer 6

USE <dbname>
GO
CREATE ROLE NEWROLE AUTHORIZATION dbo
GO
GRANT EXECUTE ON [dbo].[GetSPROC] TO NEWROLE
GO
CREATE USER [NEWUSER] FOR LOGIN [NEWUSER] WITH DEFAULT_SCHEMA=[NEWUSER]
GO
EXEC sp_addrolemember N'NEWROLE', N'NEWUSER'
GO

Hope above resolves your issue.

Answer 7

hi Anurag,

Still I am facing the same issue.

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

Answer 8

Hi,

Are you sure you have the stored proc in the user Db? Can you run the following code but add your user DB name?

use master
go
-- Replace YOURUSERDBNAME with the correct DB name
SELECT [SPECIFIC_CATALOG]
      ,[SPECIFIC_SCHEMA]
      ,[SPECIFIC_NAME]
      ,[ROUTINE_CATALOG]
      ,[ROUTINE_SCHEMA]
      ,[ROUTINE_NAME]
      ,[ROUTINE_TYPE]
      ,[CREATED]
      ,[LAST_ALTERED]
  FROM [YOURUSERDBNAME].[INFORMATION_SCHEMA].[ROUTINES]
  WHERE ROUTINE_NAME = 'GetSPROC'

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 9

my problem is solved.

There is a statement in the stored procedure which requires special permissions and which doesn't exist for my new user.

I would thank every one, who spent time to help me.