Security issues of SQL Server alias users

SQL Server has many useful small functions, but sometimes these functions in turn cause you trouble. One of these problems is the use of Alias users. This tip teaches you how to find a security vulnerability when creating an alias user in a database. However, this feature is not recommended in SQL Server 2008.

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

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 Permissions because of a security vulnerability in SQL Server. Let me explain this question.

Let's create two logins: AliasUser1 and DBUser1.

The alias AliasUser1 is assigned to the "dbo" role.

Add User DBUser1 to the role "dbo". If we need to grant the "dbo" permission to a database user, this is a normal behavior.

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 test the user we just created from SSMS. Note: The alias "AliasUser1" in "dbo" does not appear below the user. SQL Server does not display alias users in the "user" list.

How can we find an alias user in a database? Execute the following query:

Selectfrom sys. sysusers

In the following result set, we can see that "is in front of" AliasUser1 ". If we check the field" isaliased ", in the Third Field on the right, it has a value of 1.

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

Exec sp_helpuser

Another interesting thing to note is that you cannot use the sp_dropuser stored procedure to delete an alias user. Instead, use sp_dropalias to delete the user.

Using a login with an alias to "dbo" will allow you to fully access your database, for example, a db_owner. The disadvantage is that this process can be seen in no part of SSMS. You can try it by yourself. In one of your databases, follow the steps below, and then log on to "AliasUser1" and password "test ", you can see that the login can perform any behavior it wants in the database where you create the alias.

Next step

Run sp_helpuser for your database to see if you have created an alias.

If an alias user has "dbo" level permissions, delete these and use the roles described below.

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

Therefore, if your application is using an alias that is not available in SQL 2008, use the different methods mentioned above.