CODEWITHSUNDEEP
sqlsql-serverstored-procedures
A. S. Mahadik
A. S. Mahadik

Reputation: 625

CREATE PROCEDURE permission denied in database 'master'

I am creating procedure in sql server, My code is as follows

CREATE PROCEDURE GetRegistrationId
( 
  @MaxId INT OUTPUT 
)
AS
BEGIN
  SELECT @MaxId = MAX(UserId)  FROM tblRegistration;
return;
END

but it gives error saying

CREATE PROCEDURE permission denied in database 'master'.

Upvotes: 3

Views: 19965

Answers (3)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

USE <Your Database Name>
GO

CREATE PROCEDURE GetRegistrationId(@MaxId INT OUTPUT)
AS
BEGIN
  SELECT @MaxId=MAX(UserId)  FROM tblRegistration;
RETURN;
END

OR

Select "Your Database Name" from Toolbar (SQL Editor) and then Execute the procedure

Select your DB

Upvotes: 11

Dhwani
Dhwani

Reputation: 7626

Try below of any techniques.

On top of your create procedure statement write this USE [YOUR_DBNAME]. 

USE [YOUR_DBNAME]
GO
CREATE PROCEDURE GetRegistrationId
( 
  @MaxId INT OUTPUT 
)
AS
BEGIN
  SELECT @MaxId = MAX(UserId)  FROM tblRegistration;
return;
END

or

In SQL Server, At your SQLQuery Editor choose your target database from available Database drop down list and execute your Stored Procedure.

Comment below if you still face any issue.

Upvotes: 5

Raj
Raj

Reputation: 10853

Switching context to your DB would be the best approach. Hard coding

USE <YourDB>

in the beginning of the procedure, or using a fully qualified name to include DB name will make the SP less portable

Upvotes: 1

Related Questions