Question

djuk99 on Thu, 11 Dec 2014 17:04:27


user created a stored procedure in one database and got "unable to execute stored procedure from database name blah blah", copied the stored procedure to another database and user could then execute it. User has exactly the same mapping in both databases (i.e. db_datareader, db_datawriter, db_ddladmin) and is only in the server public role. why does he get the execute permissions problem on the firdt database? I have created a new user myself with the same mapping and get the same problem.  

IGNORE THIS>> I can see what the problem is now, the EXECUTE effective permission is missing from the first database - anybody know how to create an effective permission?

Update on the above following your much appreciated comments:-

1. The full error message (with any sensitive detail masked out) is:-

Msg 229, Level 14, State 5, Procedure xxxxxxxx, Line 1The EXECUTE permission was denied on the object 'xxxxxxxxx', database 'xxxxxxxxx', schema 'dbo'.

2. The problem is one database automatically gives execute permission when a login is mapped to it (even with just the db_datareader role selected) but the other doesn't. To see the permissions I refer to, right click database select properties/permissions select the user and look at effective permissions, user has connect,execute and select (explicit permissions are connect only). The same login mapped to the other database has connect and select permissions only.

I haven't given any explicit permissions to the login mapped to the first or second database so why does the first have the explicit execute permission granted atomatically and the second not?

Even when I map the logins to the db_datawriter and db_ddladmin as well I still don't get effective execute permission on the second database. I know I can grant explicit permissions at schema or database level but do I don't have to do that on the first database. There are no specific permissions on the schemas (i.e. db_datareader/writer/ddladmin or dbo) to public role or users



Sponsored



Replies

Erland Sommarskog on Thu, 11 Dec 2014 22:06:39


user created a stored procedure in one database and got "unable to execute stored procedure from database name blah blah",

It might be your opinion that error messages are only blah blah, and it is not worth time reading them. However, there is often useful information in error messages, and next time you have a question, please include any error message in full. Even if the message is nonsense to you, it may not be to us.

I can see what the problem is now, the EXECUTE effective permission is missing from the first database - anybody know how to create an effective permission?

When you create a procedure in a schema, you don't become owner of the procedure, but the schema owner becomes owner of the procedure. And with no further rights, the creator can't execute. This can be resolved by granting the user EXECUTE permission on schema level or database level:

GRANT EXECUTE ON SCHEMA::dbo TO developers  -- schema-level
GRANT EXECUTE TO developers                 -- database-level

As the example hints, you should grant to a role rather than an individual.

Doanh, Qh on Mon, 15 Dec 2014 09:56:20


How does the second database can work with the store procedure of the first database ?? they are one database or have special relationship ?? please clarify.

if you USE[TheSecondDatabase] in the store procedure.

Visakh16 on Mon, 15 Dec 2014 11:15:38


user created a stored procedure in one database and got "unable to execute stored procedure from database name blah blah", copied the stored procedure to another database and user could then execute it. User has exactly the same mapping in both databases (i.e. db_datareader, db_datawriter, db_ddladmin) and is only in the server public role. why does he get the execute permissions problem on the firdt database? I have created a new user myself with the same mapping and get the same problem.  

I can see what the problem is now, the EXECUTE effective permission is missing from the first database - anybody know how to create an effective permission?

Is the stored procedure being created on schema on which user has access to? ALso is it database within same server? Or is it a remote server? If its a linked server you use to execute sp make sure login is properly mapped in the new db.

djuk99 on Tue, 16 Dec 2014 13:35:50


sorry for the confusion - I am creating the stored procedure separately in both databases 

djuk99 on Tue, 16 Dec 2014 18:31:04


Found the answer myself - execute had been granted to the public role on the first DB. Obviously not good from a security perspective but now I now why all users were being granted execute permission (even the read only lot) on that DB I can sort it out now and put correct permissions in place.