July 2, 2003 at 4:35 am
My Access 97 front end uses ADO to run procedures which add, delete, modify id's in the backend SQL Server table.
This ID is used for storing information like user country and also for storing some security flags.
I would like to eliminate the separate manual task of adding and deleting SQL Server logins, giving users access to databases and adding users to roles.
I thought it was just a case of adding below to my procedures:
sp_addlogin, sp_grantdbaccess, sp_addrolemember, sp_revokedbaccess, sp_droplogin, sp_password
Everything worked perfectly when running using my id from Query Analyzer but as soon as I logged on as a normal user, I get the message "Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlogin etc"
The Access code which calls the procedures is compiled and also the role only gives very limited access to procedures and functions. I am reluctant to give superuser SYSADMIN access because of the small chance that the users will discover they could connect directly and accidently do damage.
Is there a way I can get round this restriction and run sp_addlogin etc. from a procedure, trigger or any other way?
July 2, 2003 at 4:51 am
You seem to want to empower the users with all security (but not system/server) admin abilities. Why not grant them SECURITYADMIN rather than SYSADMIN?
Cheers,
- Mark
July 2, 2003 at 4:59 am
You could add everything to a Job and start that from Access. I'm not sure under what security context a job runs if you start it manually.
July 2, 2003 at 6:06 am
Thanks for the replies so far.
I don't want to give access to all security, only execute access to procedures within a single database plus anything else they need to get there.
I am sorry if this is a stupid question but I am fairly new to SQL Server.
What is a job? How do I create it? How would I pass the user ID to the job?
If you could give me the basic steps 1,2,3 then I am sure I can dig in the documentation and work it out.
By the way I also tried using triggers. i got the trigger to run when I added a new id to the table but I discovered that sp_addlogin would not run in a trigger.
July 2, 2003 at 7:29 am
You can create jobs using Enterprise manager. Go to your server->Management->SQL Server Agent->Jobs.
You can add a new job. It acts much like any scheduler kind of stuff. You can set general properties and add one or more steps (tasks). For your problem, you would need a Transact-SQL step that calls a stored procedure.
To get data to that step is a bit trickier. I think the best solution would be to construct a intermediary table to store all information that you need to create the user (e.g. name, password, ...). The procedure you start will get its information from there.
Your application first inserts the data in the table and then starts the Job using SQL-DMO.
July 2, 2003 at 9:26 am
To test this out, I create a job 'AddUser' which just contained the code sp_addlogin 'test'.
Not sure whether it is possible to use SQL-DMO in Access 97 so I tried using Transact SQL in Query Analyzer:
Use msdb
EXEC sp_start_job @job_name = 'AddUser'
Still a SYSADMIN problem which I overcame by using xp_sqlagent_proxy_account. Managed to set this up using my account so that any user could run the job from Query Analyzer. However it is not possible to incorporate the 'use msdb' line in a procedure.
Is there a way using transact SQL to run this job from a procedures? Also are there any dangers setting up xp_sqlagent_proxy_account with SYSADMIN role.
If I can sort the above out, then processing the intermediary table should be a piece of cake.
July 2, 2003 at 10:00 am
Personally, I'd abstract it. Set up a table and have the Access front end insert users into this.
Then have a stored procedure the reads this table (top 1) and runs sp_addlogin to create the login. then marks or deletes the entry and repeats. You could schedule this to run every minute or run it continuously as a startup procedure and let it do the work.
Having a security abstraction can only help you. Not completely protect you, but it limits what can be done.
Steve Jones
July 2, 2003 at 11:00 am
Now why did I not think of that. Sounds a very straightforward solution compared to the hoops I was trying to jump through.
Running the job every minute is clear and also the logic to process the tables.
Just a couple of points which are not quite clear to me in your reply:
When you refer to repeating in the procedure. Do you mean if there was more than one new login in the table that the same procedure could loop while COUNT * > 0. Could you give me a hint as to how to write this looping.
Secondly I am not quite clear what you mean by "or run it continuously as a startup procedure"
July 7, 2003 at 8:03 am
I added the following procedure to a job running every minute. This seems to work OK but I am quite new to this so I would welcome any suggestions for improvement.
CREATE PROCEDURE procAddLogin AS
DECLARE @UserId VARCHAR (7)
DECLARE @Password VARCHAR (15)
DECLARE NewUserCursor CURSOR FOR
SELECT UserId, tmpPassword FROM SALES
WHERE (tmpAdded = 0)
OPEN NewUserCursor
FETCH NEXT FROM NewUserCursor
INTO @UserID, @Password
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_addlogin @UserId, @Password
EXEC sp_grantdbaccess @UserId
EXEC sp_addrolemember 'kbuser', @userid
UPDATE Sales SET
tmpAdded = null,
tmpPassword = null
WHERE
UserId = @UserId
FETCH NEXT FROM NewUserCursor
INTO @UserID, @Password
END
CLOSE NewUserCursor
DEALLOCATE NewUserCursor
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply