The stored procedure sp_addalias probably isn't one you use a lot, if at all.
What does it do? Directly from BOL, it says:
"Maps a login to a user in a database. sp_addalias is provided for
backward compatibility. Microsoft SQL Server version 7.0 provides roles and the
ability to grant permissions to roles as an alternative to using aliases."
That help? Basically MS recommends (and I support this idea) that you always
assign permissions to roles and then assign users to those roles. In the pre v7
days it wasn't as easy, so you could set up a user 'accountingclerk' that had
the necessary permissions, then you could use sp_addalias to give 'clerk#1' the
exact same permissions. Roles are definitely easier to work with and to follow.
So given all of that, why would you alias?
Suppose you have a user 'Steve' for whom you've created a database and
against your better judgment, you've placed in the dbowner role. Steve goes
merrily to work, creating many, many objects by running scripts like this:
create table SomeInfo (rowid int, MoreInfo text)
Now for the interesting part - who will own those objects? Steve or dbo?
Steve will. He can make them owned by dbo if he remembers to do this:
create dbo.table SomeInfo (rowid int, MoreInfo text)
Or he can do this after the fact (or get you to do it):
sp_changeobjectowner @objname='steve.someinfo', @newowner='dbo'
Now if you want the objects to be owned by Steve you don't have a problem. If
you can get the user to fully qualify all objects when they are created, you
don't have a problem. Or even if you're willing to change all of the object
owners afterward to dbo, you don't have a problem (more work maybe!). Still, in
this case if the desired outcome is to have the objects owned by dbo, aliasing
provides a much better solution.
To create an alias, the user cannot already exist in the database and
obviously the login must already exist. Then run this:
sp_addalias @loginname ='Steve', @name_in_db='dbo'
We've made Steve the equivalent of dbo. Now any object created without
qualifying the owner will automatically be owned by dbo. He still has the option
to fully qualify so that it's owned by his login directly or by any user that
exists in the database.
Another scenario where it might come in handy is where you have permissions
assigned to a user instead of a role and you need to give another user the same
exact access. The "right" thing to do is to create a role, give the role the
same permissions as the original user, move the original user into the new role
and remove the permissions from the user, then create the new user and add them
to the same role. If it's only a few objects, that's fine. What if it's 500? Or
you're in a hurry and no time to test? Create the new user and alias to the
original one, then put an item on the TODO list so you can fix it when you have
more time!
Using aliases isn't a common thing, but sometimes they are exactly the right
tool - if you know they exist. Know that you do, experiment a little so when the
time comes, you're ready. For more info on why I think everything should be
owned by dbo see the article
Worst Practices - Objects Not Owned by DBO.
