November 4, 2008 at 9:40 am
I have a Stored Procedure and the boss is going thru making SELECT STATEMENTS Dynamic now...
So I say
USE [TEST]
SELECT * FROM [TABLENAME]
HE IS NOW DOING
EXEC ('SELECT * FROM ' + @DBNAME + '.' + @TBLOWNER + '.[TABLENAME]')
OR
EXEC ('SELECT * FROM ' + @TBLOWNER + '.[TABLENAME]')
I know the USE [TEST] rids of using @DBNAME, but is there anything simiular for the @TBLOWNER... I don't want to have to have this whole thing become Dynamic...
FYI, not sure if matters, but the person that runs this SP doesn't have rights to dbo., so he is using the group name to replace the dbo. with THISGROUPSNAME., because multiple people using the table in this group.
November 4, 2008 at 1:25 pm
John, sorry - but this question does not really make any sense to me. You cannot have a stored procedure that has a USE statement in it.
Second, why would you need to change the database in the query? You should already know what database the objects are in - or, the procedure already exists in the database where it should be run.
And finally, the users would not need access to 'dbo' if you are not using dynamic SQL. The users would execute the procedure which has access to the underlying tables because the creator\owner of the procedure has access.
I just don't see the reason for using dynamic SQL - or for specifying the database either.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 4, 2008 at 1:34 pm
The keeper opens up Access and they are logged into to SQL Server with their XID, maybe even the group permissions...
He doesn't want the Table that is created by this keeper thru Access(runs a SP) for multiple Reports/users (the table is used by others in a group to report off of) with [dbo].[tablename] nor [XID].[tablename]; what he wants is the Group Name [groupname].[tablename], becuase the people running off the table that was created by the (keeper) are in a SQL Server GROUP permission thing, and non have rights to the [dbo], or the keepers [XID], but they have rights to [groupname]...
we tested creating a table with [groupname]
Does that help with the mess...lol
John
November 4, 2008 at 1:51 pm
Sorry - still doesn't help. If the users are executing a stored procedure - then it should not matter what objects are accessed by the procedure.
The only time it matters whether or not the users have access is when you start using dynamic SQL.
I still don't see any reason for using dynamic SQL in this case.
BTW - if you upgrade to 2005/2008 you could look at setting up different schemas and creating synonyms. That would probably solve your problems - but, it looks like you are stuck on 2000 and wouldn't be able to use them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 4, 2008 at 2:01 pm
I'm trying to tell him not to use Dynamic SQL
He declares the @DBName and the the @DBSchema [dbo]
the DBName is ok cause we use USE
Only one person runs this SP to create the table...
The users in this group that run reports off this table don't have rights to [DBO] or this person [XID]
Through out the SP, I use [dbo].[fn_name], [dbo].[tablename]... then at the very end he query's the user who created the table to get the user group name (from his table of authorizations) and then sets that to the variable @DBSchema to append that to the table name, thus using [groupname].[tablename]....
because by default if she creates a table it has her [XID].[tablename], he wants to override this default, to use @DBSchema that he queried...
am i getting closer...lol
John
November 4, 2008 at 2:37 pm
Unfortunately, I have absolutely no clue as to why this is being done. Are you really calling a procedure in one database, that needs to access data in another database?
And you keep saying that the users who run the reports don't have access to the table being created by the procedure. I don't understand why the table is being 'generated' or built this way.
Sorry - I really have no idea how to help you with this. Looks to me like your boss is deliberately making this harder than it has to be.
Why not just create views and grant select access on the views? This dynamic stuff is just asking for lots of trouble in my opinion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 4, 2008 at 2:42 pm
1st, you don't need USE.
Just point on required database when call procredure :
EXEC DatabaseName.dbo.ProcedureName
2nd, as soon as you know the name of user who creates the table(s) just inside procedure explicitly qualify name of procedure with user name:
SELECT Blah, Blah
FROM XID.Table
User may wish to give read access to this table to public when table is created.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply