August 18, 2005 at 10:03 am
Hi
I am in the process of changing a large .mdb (2000) to an Access 2003 .adp (then to ade).
I've managed to sucessfully create a subsidiary database - which works excellently. However, now I try it on user machines I have a slight problem, which I'm hoping might be easy to overcome.
Most of my forms are based on sps, which pass back input parameters. All works brilliantly on my own machine - but returns a 'can't find sp' message on user machines. I've discovered that if I put dbo.spname in the form RecordSource, it will work fine.
In the old mdb all tables were dbo (on the server) but the dbo didn't need to be declared in Access forms etc.
Do I have to go through and make all sp references dbo.name, or is there an easier way?
Also, I guess all users will need to be given the right to execute the sp on the server?
Many thanks
Paul
August 18, 2005 at 10:06 am
I'm surprised it even worked in the 2000 version.
You should ALWAYS, read ALWAYS specify the owner of the object (access, vb, sql...). And yes they'll need the permissions accorded to run them.
August 18, 2005 at 10:11 am
You might find this usefull :
--This will grant permission to the db_executor group for every stored proc in the system that he has no access permission to without changing any deny permission
--this will grant permission only to objects that needs to have it (will not set the permission again if the object already has it
CREATE PROCEDURE [dbo].[GrantDbExecutor_NewObjects_SP]
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000),
@Owner sysname,
@StoredProcedure sysname,
@Return int
-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD LOCAL READ_ONLY
FOR
SELECT USER_NAME(so.uid) Owner, so.[name] StoredProcedure
FROM dbo.sysobjects so
LEFT JOIN
(SELECT id
FROM dbo.sysprotects
WHERE uid = USER_ID('db_executor')
AND action = 224) sp
ON so.id = sp.id
WHERE so.xtype = 'P'
AND sp.id IS NULL
OPEN cursStoredProcedures
-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure
-- Set the return code to 0
SET @Return = 0
-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN
-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
BEGIN
-- Create the SQL Statement. Since we're giving
-- access to all stored procedures, we have to
-- use a two-part naming convention to get the owner.
SET @sql = 'GRANT EXECUTE ON [' + @Owner
+ '].[' + @StoredProcedure
+ '] TO db_executor'
-- Execute the SQL statement
EXEC @Return = sp_executesql @sql
-- Get the next row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure
END
-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures
-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
BEGIN
-- Exited fine, commit the permissions
COMMIT TRAN
END
ELSE
BEGIN
-- Exited with an error, rollback any changes
ROLLBACK TRAN
-- Report the error
SET @sql = 'Error granting permission to ['
+ @Owner + '].[' + @StoredProcedure + ']'
RAISERROR(@SQL, 16, 1)
END
SET NOCOUNT OFF
GO
Manual version. I run automatically when I redeploy my ADPS, I have a listbox on a form that is linked to that query. I can then manually grant permission if I forgot it (select the list to grant, loop trough the list, and execute the last column if selected) :
SELECT TOP 100 PERCENT USER_NAME(so.uid) AS Owner, so.name AS StoredProcedure, so.XType, so.refdate AS CreationDate, 'GRANT ' + CASE WHEN so.XType in ('IF', 'TF') THEN 'SELECT ON [' ELSE 'EXECUTE ON [' END + USER_NAME(so.uid) + '].[' + so.name + '] TO [Domain Users]' as GrantStatement
FROM dbo.sysobjects so LEFT OUTER JOIN
(SELECT id
FROM dbo.sysprotects
WHERE uid = USER_ID('Domain Users') AND action in (193,224)) sp ON so.id = sp.id
WHERE (so.xtype IN ('P', 'FN', 'IF', 'TF')) AND (so.status >= 0) AND (sp.id IS NULL)
ORDER BY so.name
August 18, 2005 at 4:57 pm
Remi, That's a very nice piece of code. Thanks!
Paul, just to add a bit to Remi's comments:
Each Access form has a property called RecordSourceQualifier. This is the default object owner for all SQL statements on the form. You may notice that the Query designer does not use owners; apparently Access will add the owner name when the query is sent to the server. If you have more than one person who owns tables etc, you will need to look into this. For example, I don't know how it handles combo boxes - we will need to try the profiler again to see if Access is doing this properly.
Having said all that, I don't understand why your unqualified SPs don't work from another user's machine. I suspect that it is a permissions problem, and not an owner problem. Since you are presumably the dbo and owner of all the objects (I'm guesing here), the forms should work. If you are not the dbo and owner, then everything must be qualified.
In general though, I agree with Remi, you will save yourself a lot of trouble, and possibly improve performance a bit, if you always try to use the owner qualifier.
August 19, 2005 at 7:03 am
There's a lot more performance improvement that meets the eye with the use of qualified name, there's a lot to do with plan reuse to.
August 19, 2005 at 9:00 am
Thanks to both of you.
Rich
I have ensured that the RecordSourceQualifier is dbo in each case, and checked permissions. However, this still didn't work.
I then changed all my sp references to dbo.spname - and similar in code. It now all works fine. I'm not sure why this is, but the RecordSourceQualifier property doesn't appear on the user machine.
Remi
I now take your point about references....
Your code looks really useful, but I am not yet sufficiently practiced to make it work. I tried it in Query Analyser, and made the sp dbo.GrantDbExecutor_NewObjects_SP
I'm sorry to have to ask you how I should use it.
Thanks and apologies
Paul
P.S. I now have another question, but will start a new thread as you both have suggested before....
August 19, 2005 at 9:08 am
The sp works in automatic mode, it grants access to the db executor role to the sps (that role must exists on the server, or you can change that to domain users). change the exec to print and run manually to see what's wrong.
August 19, 2005 at 9:11 am
"RecordSourceQualifier property doesn't appear on the user machine."
Is it possible that the user is using an older version of Access? That type of version conflict can be a serious problem.
August 19, 2005 at 9:15 am
Also, I was always suspicious of the RecordSourceQualifier property. I'm still not sure that it really does anything.
The whole issue is strange, since I don't have that problem when I don't use dbo. But to be on the safe side, I'll now have to go through my projects, and see where I've left off the dbo's!
This requires a bit more investigation...
August 19, 2005 at 9:21 am
What are the permissions given to the users?
August 19, 2005 at 9:38 am
I just ran a trace to test RecordSourceQualifier. Looks like it does nothing at all. And the SQL editor doesn't use it either. In fact, the SQL editor just deletes any owners that you type in. Pretty nasty bug, I thing. You can still insert "dbo." in the property window though. What a pain.
I just submitted the bug to MS.
August 19, 2005 at 9:40 am
And what permission do your users have on the system??
August 19, 2005 at 9:54 am
Rich
You are right, there are a mixture of versions on the system. The RecordSourceQualifier does exist on 2003 systems. However, following all advice, using dbo works for all. I will continue to use it in practice.
Remi
Fantastic piece of code. Of course it was easy - when I saw it work.
A great many thanks to you both.
Paul
August 19, 2005 at 9:57 am
HTH.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply