SQL Server Cannot create stored procedures

v64noz0r 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(116)

I have an Azure SQL database. I would like to allow an Azure AD group named sql-executable to be the owner of the database structures_db_test .

So I did the following:

Created a login in the master and a user also on master both named sql-executable .
Then on structures_db_test , I ran:

CREATE USER [sql-executable] FROM EXTERNAL PROVIDER;

ALTER ROLE db_owner ADD MEMBER [sql-executable];

But now, when trying to create a new stored procedure I get this error:
Msg 2760, Level 16, State 1, Procedure hello, Line 1 [Batch Start Line 0]
The specified schema name "[my azure upn name]" either does not exist or you do not have permission to use it.

I cannot understand why I get this error when trying to create a stored procedure after doing the above

It is very weird because it is a member of sql-executable 100%

I can run existing stored procedure queries but not creating new ones.

sql-server

来源：https://stackoverflow.com/questions/75652851/cannot-create-stored-procedures

1条答案

按热度按时间

xytpbqjk1#

The specified schema name "[my azure upn name]" either does not exist...

You solve this by:

CREATE SCHEMA [my azure upn name];

If you want to make your user the owner of the schema:

ALTER AUTHORIZATION ON SCHEMA::[my azure upn name] TO [sql-executable];

But they should be able to create objects in that schema anwyay via db_owner . Just make sure they always specify the schema in all object references, e.g.:

CREATE OR ALTER PROCEDURE [my azure upn name].ProcedureName
...

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Cannot create stored procedures

1条答案

相关问题

热门标签

最新问答