September 26, 2003 at 8:33 am
I am allowing security administrators to add new users by adding them to a table 'Sales'
The below Procedure works well when executed within the QueryAnalyzer albeit with warning messages where the login already exists or the user exists in the database e.g.
quote:
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56The login 'A048LP' already exists.
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 126
User or role 'A048LP' already exists in the current database.
'A048LP' added to role 'kbuser'.
(1 row(s) affected)
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'A050HGU' already exists.
Granted database access to 'A050HGU'.
'A050HGU' added to role 'kbuser'.
(1 row(s) affected)
I found however that when I execute the same procedure in DTS or from a job, then only a small number of the user ids are processed.
Would appreciate advice as to how to rewrite this procedure so that it works in all circumstances.
I do not need to know if the login exists or if the user already a member of the database.
ALTER 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
September 26, 2003 at 1:27 pm
I believe your DTS is erroring out because it comes across a user that already has been set up which kicks out a error. This error then stops the DTS from preceding any farther. To fix this, you will need to add some checks to see if the user exists at each step. Below you will see the checks that I think you need.
ALTER PROCEDURE procAddLogin AS
DECLARE @UserId VARCHAR (7)
DECLARE @Password VARCHAR (15)
DECLARE NewUserCursor CURSOR FORSELECT UserId, tmpPassword FROM SALESWHERE (tmpAdded = 0)
OPEN NewUserCursorFETCH NEXT FROM NewUserCursorINTO @user-id, @Password
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(suid) FROM Master..SysLogins WHERE name = @UserId) = 0
EXEC sp_addlogin @UserId, @Password
IF (SELECT COUNT(uid) FROM SysUsers WHERE name = @UserId) = 0
EXEC sp_grantdbaccess @UserId
IF (SELECT COUNT(a.uid) FROM SysUsers a INNER JOIN SysMembers b ON a.uid = b.memberuid
INNER JOIN SysUsers c ON b.groupid = c.uid AND c.name = 'kbuser'
WHERE a.name = @UserId) = 0
EXEC sp_addrolemember 'kbuser', @userid
UPDATE Sales
SET tmpAdded = null,
tmpPassword = null
WHERE UserId = @UserId
FETCH NEXT FROM NewUserCursorINTO @user-id, @Password
END
CLOSE NewUserCursorDEALLOCATE NewUserCursor
September 29, 2003 at 7:51 am
Thanks, that worked perfectly. I just changed the first check to
if (SELECT Count(sid) FROM Master..SysLogins WHERE name = @UserId)=0
because I think there was a typo. This now runs in DTS without any errors.
Just one more question if I may.
I also need a script to delete users.
if (SELECT Count(uid) FROM sysusers WHERE name = @userid) > 0
EXEC sp_revokedbaccess @userid
Works fine to remove a user from a database
But what check would I need before:
EXEC sp_droplogin @UserId
To check whether the login was also a user in other databases.
Thanks
Stefan
September 29, 2003 at 1:38 pm
Stefan,
Good question, I am not sure of an easy way to do it other then looping through each DB on the SQL Server.
I believe there is a way to do it using 'sp_MSforeachdb'. Basically, I would set it up to count the DB the user has access to. If the answer is 0, then drop the login, otherwise, do not drop it until further investigation of the other DB's.
I could have a solution for you tomorrow following this premise. Talk with then.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply