January 15, 2003 at 9:37 am
I'm trying to troubleshoot some issues our helpdesk is having in creating new SQL logins or changing passwords (SQL 7). The helpdesk group has security admin privileges. When I run sp_change_users_login 'report', no records are returned for any of the dbs on the server, yet I get records returned when querying syslogins and each of sysusers tables. Is this symptomatic of a corrupt master?
BOL cautions against using the 'auto_fix' parameter with sp_change_users_login, so I haven't tried that.
Thanks,
Rob Haas
January 15, 2003 at 9:44 am
sp_change_user_login 'report' is used to lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.
If your help desk create login and grant them to access corresponing databases correctly, you won't be see anything from this sp.
January 15, 2003 at 12:33 pm
Thanks for that lightning-fast reply. I guess I'm barking up the wrong tree then.
The problem is that when someone with security admin status creates a NEW login, they are asked to enter the OLD password as well as the new password. If the security admin then enters the password in both places, and clicks OK, an error message is generated which states the user must be a member of the sysadmin role or the database owner can execute this stored procedure. The admin clicks OK, and the login is created. Upon checking the user's status, no db's are selected for access. If the admin tries to add a db for access, the same error message is displayed. If the admin then attempts to change the password, the following error is generated: "only members of the sysadmin role can use the loginame option. The password was not changed." And it is not changed. If user tries to connect, they are given an error saying that a connection could not be established to server, and that default db '<ID>' could not be opened; and the connection fails.
I have specifically given security admins exec rights on sp_addlogins, sp_addrolemember, sp_change_users_login, sp_defaultdb, and sp_password in an attempt to fix this, but to no avail. Sysadmins have no problem creating logins or changing passwords.
A select on the user name created by the security admin from syslogins tells me that the login should work, since the login name is there, hasaccess = 1, and the default db is listed correctly.
This problem only began recently but I have not yet been able to determine what may have changed on the server. Also haven't found anything on support.microsoft as of yet.
Thanks,
Rob Haas
January 15, 2003 at 1:53 pm
You need permission to run sp_grantdbaccess to grant the login you created to access database.
Only members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles can execute sp_grantdbaccess.
January 15, 2003 at 2:47 pm
But in BOL,
Members of the Microsoft® SQL Server™ fixed database role db_securityadmin have permission to modify role membership and user permissions in the database.
And according to an article I found on MSDN by Richard Waymire and Ben Thomas called "Microsoft SQL Server 2000 Security":
The SecurityAdmin role has the right to change the passwords of SQL Server Authentication mode logins. The exception to this is that the passwords of sysadmin fixed-server role members cannot be reset. For example, this would make the SecurityAdmin role more useful for someone in a help-desk job who does not need full system administrator access to SQL Server.
What I get from this is that someone with securityadmin and db_securityadmin status should have all the rights necessary to create and modify login and add users to the db.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply