First a little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry Dyck
It looks like you connect to the database as a member of PWNDAdmin and try to grant the role permissions you don't have. The error you were getting is caused by running as a low privileged principal with no access to the procedures. You don't need to grant db_owner status to grant execute permission, you just need to be able to grant permissions on the object and one way to achieve this would be to connect to the database as the dbo before granting the permissions.
Thanks
Laurentiu
Thanks,
Gerry
|||
But didn't you get db_owner status because you're a PWDNAdmin member and you made that a db_owner? Or were you a db_owner before that?
As a db_owner, you should be able to grant any permission on objects in the database.
Can you post the T-SQL grant statement that fails as well as the text and number of the error that you are receiving?
Thanks
Laurentiu
The T-SQL is:
GRANT EXECUTE ON mystoredprocedure TO PWDNAdmin
To get the error I will have to get back to the office and run the program. I will do that tomorrow.
Thanks for your help in deciphering my problem. Sure as tomorrow is the shortest day, my problem will be some simple thing, but until I find it....uuuuggghh.
Gerry
|||
Maybe the mystoredprocedure resides in a special schema and you should prefix its name with the schema name. The statement may fail because it cannot find the procedure within the schemas it searches by default.
Thanks
Laurentiu
|||I am not sure about the schema that the query belongs to.
Maybe I need to review, for myself as well as for you to help me better. The database I am using was upgraded from MSDE so it brought along all the permissions for PWDNAdmin and the stored procedures. One of my programs that has been using the database for a few months works without any problems after the upgrade to SQLExpress.
Now I have created stored procedures in a separate database located on my notebook (so I can develope at my home office) that I have then created in the database in question.
Here is the T-SQL that I used to create the procedures (well only one 'cause there are lots)
STart:
USE [ProcWareDOTNET]
GO
/****** Object: StoredProcedure [dbo].[AcctInitialLogSelect] Script Date: 12/21/2005 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[AcctInitialLogSelect]
AS
SET NOCOUNT ON;
SELECT InvoiceNumber, PONumber, DateReceived, VendorName, Value, Initials, ErrorDescription FROM ATInitialLog
Grant Execute on dbo.AcctInitialLogSelect to PWDNAdmin
END
Note the Start and End are so you know where the sql ends. Anywho. I have now discovered the problem. If I remove the Grant Excecute line then my problem goes away!!! Now why would that be? Very interesting.
|||
The grant ends up being part of the procedure definition, so when you will call the procedure, you will actually perform a grant on it. You should always use BEGIN and END to delimit the body of your stored procedure. Also, simply adding a GO before the grant would make the script run as intended, but I recommend always using BEGIN and END.
Thanks
Laurentiu
Thanks again have great holiday season and terrific new year,
Gerry
|||
I am glad I could be of help. Happy holidays to you as well.
Thanks
Laurentiu
