Security issues with alias users for SQL Server

SQL Server has a lot of handy little features, but sometimes these features can turn you into trouble. One such issue is the use of alias users. This tip teaches you how to find a security vulnerability when you create an alias user in a database, but this feature is not recommended in SQL Server 2008.

First, you can run the following simple query for each database or run it against all databases to find users with "dbo" access.

SELECT DISTINCT db_name () database_name,

Su.name Database_username

From SYSUSERS SU

JOIN sysmembers SM

On SM. Memberuid = SU. Uid

INNER JOIN sysmembers SM1

On SM. Memberuid = SM1. Memberuid

INNER JOIN SYSUSERS SU1

On SM1. Groupuid = SU1. Uid

and su1.name = ' db_owner '

and Su.name ' dbo '

However, this query may not find all users with "dbo" access because of a security vulnerability in SQL Server. Let me explain the problem.

Let's create two logins, AliasUser1 and DBUser1.

Alias AliasUser1 assigned to the "dbo" role.

Adding user DBUser1 to the role "dbo" is a normal behavior if we need to grant "dbo" permissions to a database user.

The following script will do these actions for us:

IF not EXISTS (SELECT

From SYS. Server_principals

WHERE NAME = N ' AliasUser1 ')

CREATE LOGIN [AliasUser1]

With Password=n ' Test ', Default_database=[master], Default_language=[us_english],

Check_expiration=off, Check_policy=off

Go

IF not EXISTS (SELECT

From SYS. Server_principals

WHERE NAME = N ' DBUser1 ')

CREATE LOGIN [DBUser1]

With Password=n ' Test ', Default_database=[master], Default_language=[us_english],

Check_expiration=off, Check_policy=off

Go

--add a aliased user with dbo priveleges

exec sp_addalias ' AliasUser1 ', ' dbo '

--add a regular DB user to the DBO role

CREATE USER [DBUser1] for LOGIN [DBUser1]

Now, let's examine the user we just created from the SSMs. Notice that the "AliasUser1" in our alias to "dbo" does not appear below the user. SQL Server does not display an alias user in the Users list.

So how do we find an alias user in a database? Execute the following query:

Selectfrom sys.sysusers

In the following result set, we see "AliasUser1" preceded by a "", if we check the field "Isaliased", on the right side of the third field, then it has a value of 1.

The following command can also be run to display all the users and aliases. In the following output, you can see that the alias user "AliasUser1" does not appear as a user, but in the second result set it appears as an alias user.

EXEC Sp_helpuser

Another interesting point to pay attention to is that you can't use the Sp_dropuser stored procedure to remove an alias user, instead, use Sp_dropalias to remove it.

Using an alias to "dbo" through a login will give you full access to your own database, such as a db_owner. The disadvantage is that there is no place in the SSMS to see the process. You can try it on your own. In one of your own databases, follow the steps below, and then use login "AliasUser1" and password "test", and you can see that the login can do whatever it wants in the database where you create the alias.

Next step

Run Sp_helpuser against your database to see if you've created an alias.

If an alias user has the "dbo" level of permissions, then look at deleting these and using the roles described below.

This alias has been blocked by Microsoft for a long time, but it is still used in 2005. I think this is based on backward compatibility considerations. This feature was canceled in SQL 2008.

So if your application is using aliases and it is not available in SQL 2008, try the different methods mentioned above.