November 11, 2008 at 7:32 am
We use MS Dynamics GP. When Users are created using the front end, it creates an SLQ Login and users on the relevant database. However we have a database that is used to link data from the GP Database and another application database in reports. Our users need to be able to access the data in the the link database.
Up to now, we've been using sp_adduser to do the business, but I've found that certain lazy administrators forget to do this, and we get support calls from new users fairly regularly. I would therefore like to do something automagically.
Triggers came to mind, but the following:
Create Trigger Add_to_LinkDB
on Dynamics.sys.sysusers
after insert
as
sp_adduser (select [name] from inserted),, dyngrp
Results in an error, I think because it's trying to create a user in the Dynamics Database, not the LinkDB.
Can anyone give me some pointers as to how better to do this please?
November 11, 2008 at 8:43 am
Likely the issue is the Dynamics user doesn't have rights to create the user. You are having database issues.
What you might want to do if you sort out rights is create a proc in the LinkDB database that creates a user (calling sp_adduser). Then call that in the trigger.
Note that I don't like this since you are elevating permissions that you might not need to. I would consider just dropping a row in a talbe and have a backend job that comes along every minute, finds the new row, creates the user, marks it done, etc.
November 11, 2008 at 8:52 am
I don't believe that you can add a trigger to sys.sysusers as it is a system view NOT a table.
You can use a DDL trigger on CREATE_USER then you can use the EVENT_DATA() to fire the CREATE USER statement or sp_adduser. You would likely need to use dynamic SQL so you can change database context.
I would actually recommend against adding the user directly in the trigger. I would use the DDL trigger to load the user information into a staging table and then have a job that reads the table and adds the users to the new database. You may be able to do this with Service Broker, but I have not used Service Broker yet, so I can't recommend how to do that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2008 at 9:21 am
Thanks Gents..
Steve, I think you're getting the worng idea of what my problem is.. It's not a privs problem, I can sort out the privs at a later date, since it's really only SA that is able to create logins using the GP Front end in the first place, the issue is how to get a trigger to create a user connected to a login when a user is created in another database for that login.
Jack, thanks, sounds like you're on the right track.. Can you give me an example of the syntax please?
November 11, 2008 at 10:07 am
Sure the syntax for a DDL trigger is like this:
Create Trigger dbo.user_log
On Database
For Create_User
As
Insert Into logging_table
(
sql_command
)
Select
EventData().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)')
This will log the actual Create User statement which you could then run in the other database, followed by the adding to a role.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2008 at 10:16 am
Thanks Jack.. Not quite as succinct as I was after, but it'll certainly get me where I wanna be.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply