May 29, 2011 at 8:49 pm
Hi,
I'm not sure if this is a bug in SQL or something I should tweak in our database/s. Here is the scenario:
1. I'm setting up the security for an AD Group for three databases.
2. The AD group is DOMAINNAME\Domain Users. I've included this as login user in my SQL Server.
3. The AD group has two users (Non_AdminUser_1 and Non_AdminUser_2).
4. There is a SQL database user to all the concerned db for the AD group. This db user is called "Restricted_UserDB".
5. When I execute an update statement to a table that has a trigger, it fails. The trigger contains a statement like this: SELECT * INTO tmpTable FROM Table. There error message says the tmpTable is an invalid object. It appears to me the table was not created.
It works only if I do any of these:
1. Instead of using an AD Group, I applied the same security to a specific AD user. When I execute the update statement on the same table, it works.
2. Alter the trigger to use #tmpTable instead of tmpTable. This is using a temporary table instead of creating a real table. The trigger works when I am logged in as Non_AdminUser_1 (a member of the AD group).
I know the best solution perhaps is to use dbo.tmpTable. However, there is a lot of existing triggers/stored procedure that does not use the dbo schema (dbo.x format) on table/view INTO/FROM clauses. It may take me more time to review the thousands line of codes. 🙁
Perhaps anyone has a better or faster way to resolve this issue?
Warm Regards,
Feb
May 31, 2011 at 8:57 am
SELECT INTO creates a table. The groups don't have the permission to create a table, is my guess. But that's not something you usually give out. Does using the temporary table solve the issue and provide the functionality you want?
K. Brian Kelley
@kbriankelley
May 31, 2011 at 7:16 pm
Hi K. Brian Kelley,
Thank you for the response.
There is a permission for the AD group to create a table. In fact, applying the same permissions to an AD user allows the trigger to run successfully. Reconstructing the trigger to use a temporary table works. However, what makes me wonder why it works when using an AD user but it does NOT work on an AD group.
Warm Regards,
Feb
May 31, 2011 at 7:49 pm
Hi,
Another interesting thing to note. It also throws this error message when the trigger is ran:
Msg 2760, Level 16, State 1, Procedure utg_TriggerName, Line 12
The specified schema name "" either does not exist or you do not have permission to use it.
Warm Regards,
Feb
May 31, 2011 at 8:15 pm
Februarius Montefrio (5/31/2011)
Hi,Another interesting thing to note. It also throws this error message when the trigger is ran:
Msg 2760, Level 16, State 1, Procedure utg_TriggerName, Line 12
The specified schema name "" either does not exist or you do not have permission to use it.
Warm Regards,
Feb
And that is the reason why this isn't working. You cannot set a default schema for an AD group, but for an AD user you can. Your code does not specify a schema - so, the code is going to try and create that table in the user's default schema and there isn't one.
Change your code and use 2-part naming to specify the schema where the table exists.
BTW - I am not sure why you would be using SELECT INTO in a trigger. If you are creating a temp table, then there shouldn't be any issue - but it looks like you are creating a permanent table. If you are creating a permanent table - then you are going to have an issue when you have more than one process executing that trigger.
I would recommend that you review how the trigger is constructed and think of a different way of getting the desired results. If you have issues with that, post the trigger code here and we can help.
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
May 31, 2011 at 10:13 pm
Hi Jeffrey Williams,
Thank you for the recommendation. I would love to do that but there are thousands of line of codes that I need to work with to convert the all scripts to use the 2-part naming. Yeah, I know. I'm in an uneasy situation to discover concerns like this after all things are done.
As for the SELECT INTO in a trigger, you are right. I need to change it to use a temporary table instead of a permanent one.
I wish there is a way to specify a default schema for AD groups. Things are working on AD users so I thought it might be better to specify a default schema for the AD groups instead of us changing all the scripts and re-testing the whole thing again.
Anyway, I appreciate your help. 🙂
Warm Regards,
Feb
June 2, 2011 at 11:04 am
Hi Feb,
One quick thing you may try is to put your query into Query Designer. When you click ok from there, it should put the schema in front of all your objects. May save you some time...
Jason
June 5, 2011 at 9:09 pm
Hi Jpoe,
Thank you for the suggestion. However from my end, it is not adding the schema infront of my objects. I'm using the SQL 2008 R2 Management Studio to connect to a SQL 2005 database. How did it work on your end?
Here is what I did:
1. Create a new query
2. Click the menu: Query > Design Query in Editor
3. Paste my query in the editor.
4. Clicked the OK button. It did not add the schema name infront of the table.
Warm Regards,
Feb
June 6, 2011 at 9:17 am
I usually do it just like you posted, and I reviewed myself. Sorry for the misdirection, but I think I was getting confused with creating/editing a view.
Jason
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply