Wizz’s Coding Solutions

I previously posted about using sp_change_users_login to repair users that have become disconnected from their logins eg. after transferring a database from one server to another.  I’ve just noticed that Microsoft have said that they will be removing sp_change_users_login from future versions of SQL Server and to use ALTER USER instead, so I thought it would be interesting to look at the functionality of the 2, first of all to see how to use ALTER USER to achieve the same functionality as the ‘auto_fix’ and also to see what other functionality is available in the 2 alternatives.

Just a bit of background first in case you aren’t clear on the SQL Server terminology.  A login consists of a username and some means of authenticating (usually a password or Windows authentication, but it could be a certificate or key).  It also has a default database and language.  It is stored at the server level and can have server wide roles.  This doesn’t affect the rights within a specific database though.

A user has a name and is connected to a login.  It is stored at the database level and it has particular roles for that specific database that affect the permissions for that user.  A login can have multiple users, but a user can only have one login.  When you log in as a specific login and try to access a database, SQL server looks to find a user in that database that maps to the login and applies permissions appropriately.

When you move databases from one server to another, reinstall SQL server as a new instance or similar situations, the user and the login become disconnected and you can’t login any more.  In principle, you could just delete the user and recreate it from the login, but that isn’t always practical, either because you have lots of them to do, or commonly because the user owns certain objects in the database and it isn’t in a state where it can be deleted.  In practice, therefore, it is necessary to have a way to reconnect a user and login.

My previous post was about how to reconnect these using sp_change_users_login with the ‘auto_fix’ parameter, but as I have said, Microsoft are removing that and we are supposed to use ALTER USER instead.

So what else can sp_change_users_login do apart from reconnecting users and logins?  It has 4 parameters: -

1. @Action
2. @UserNamePattern
3. @LoginName
4. @Password

The @Action one is the one that we’ve been using as ‘Auto_Fix’ (capitalisation isn’t important).  There are 2 other valid values for @Action, which are ‘Report’ and ‘Update_One’.  I’ll come back to these in a minute, but first, let’s see what else we can do with ‘Auto_Fix’.

The ‘Auto_Fix’ action has a second usage – if you specify a password in @Password then it will create the login if it doesn’t already exist.  If the login does already exist then @Password is ignored.  Either way, @LoginName must be left out or explicitly set to NULL.

The ‘Report’ action reports on any users that aren’t linked to a login.  @UserNamePattern, @LoginName and @Password should all be set to NULL or left blank.  This is useful if you want to check to see if there are any unmapped users that can be deleted or if anything has gone wrong and users have been disconnected.  This also returns the security identifier (SID) with the username.  The SID is SQL Server’s ID field for keeping track of users.  This is stored as a GUID, which is a binary field.  I’m not going to post about GUIDs here, but I may post more about them in future.

The final type of action is ‘Update_One’.  This attaches a user to a specific login.  @UserNamePattern must be set to the correct user, @LoginName must be set to the correct login name.  @Password should be left out or NULL.  Presumably this will re-map a user that is currently connected to an existing login to a different one if you want, but I haven’t actually tested this.

That’s everything that sp_change_users_login does.  The question is, how to reproduce this functionality with ALTER USER and any methods that aren’t marked as obsolete.  I see there being 5 possible operations here: -

1. reattach a user to a matching login name by just specifying the user (‘auto_fix’)
2. create a login name for a user by specifying a password (‘auto_fix’)
3. reattach a user if the login exists and if not then create it with the specified password (‘auto_fix’)
4. find users that aren’t attached to a login (‘report’)
5. connect a user to an existing login (‘update_one’) (I don’t think it is relevant whether or not the user is currently connected – this is still one operation)

I’ll go through these one at a time (please note that this bit isn’t tested, so it might need some correction, but it should work in principle)…

reattaching a user to a login: -

ALTER USER username WITH LOGIN = loginname

where username and loginname are the same.

create a login name for a user by specifying a password: -

CREATE LOGIN loginname WITH PASSWORD = password;

GO

ALTER USER username WITH LOGIN = loginname;

Again, where loginname and username are the same and password is the password.

reattach a user if the login exists and if not then create it with the specified password: -

The simplest way of doing this is actually to just do the same as above -

CREATE LOGIN loginname WITH PASSWORD = password;

GO

ALTER USER username WITH LOGIN = loginname;

Again, where loginname and username are the same and password is the password.  If the login exists, the first statement will throw an error, but the second one will use the login anyway.  This is fine if you don’t care about errors.  If you want something a bit more robust then you’d have to do something like using IF EXISTS on a query to find the name in master.sys.server_principals, checking that you are looking at a type of principle that is a login type and not a role or anything else.  If it doesn’t exist then use the above top line to create it, then end the IF and then either way run the ALTER USER statement to attach the user to the login.

to find users that aren’t attached to a login: -

select UserName = name, UserSID = sid from sysusers
where issqluser = 1
and   (sid is not null and sid <> 0x0)
and   (len(sid) <= 16)
and   suser_sname(sid) is null
order by name

ALTER USER username WITH LOGIN = loginname;

Bookmark this post