After few posts devoted to indexes it’s time to look at something else. Let’s see some common problems with users and logins, and methods of overcoming them. These are the basic and most frequently occuring ones.
ALTER LOGIN [testLogin] WITH DEFAULT_DATABASE=[master]
sp_change_users_login 'update_one', login_name, user_name
ALTER USER user_name TO LOGIN = login_name
ALTER AUTHORIZATION ON DATABASE::DBOUserTest to [login_name]
A few words about required privileges to complete these actions. ALTER LOGIN requires ALTER ANY LOGIN permission, but a principal can change it for its own login (this may fail if tried from SSMS since it sets CHECK_EXPIRATION and CHECK_POLICY which require CONTROL SERVER permission, at least it’s what I observed on SQL Server 2005). To remap a user with ALTER USER, you require CONTROL permission in a database. sp_change_users_login requires membership in db_owner database role. Finally, ALTER AUTHORIZATION requires TAKE OWNERSHIP on the database (on the object, in general) and might require IMPERSONATE, if you’re not logged with login taking ownership. sp_changedbowner might need CONTROL SERVER in addition to that, if new owner doesn’t have a user in a database. Since a DBA probably is a sysadmin, this is not a problem, but I have seen users in live databases yielding CONTROL which might make us wonder “do they really need it?”.
Well, this would be all about users and logins for now. If you have questions, suggestions or corrections, feel free to comment and ask. I’ll be happy to respond.
Tags: DBA, SQL Server, users, security, logins,