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.