Security issues when using aliased users in SQL Server

By: Ranga Narasimhan | Comments (2) | Related: > Security

Problem

SQL Server has a lot of little features that are nice to use, but sometimes these things come back to get you. One such issue is the use of aliased users. This tip shows you how to find security holes when aliased users are setup in your databases and also that this feature will be deprecated in SQL Server 2008.

Solution

To begin with you can run this simple query below against each database or automate it to run 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'

But this query may not find all users with "dbo" access, because of a security hole in SQL Server. Let me explain this.

Let's create two logins, AliasUser1 and DBUser1.
Alias AliasUser1 to the "dbo" role.
Add the user DBUser1 to the "dbo" role, which is the normal practice if we need to grant "dbo" privileges to a database user

The script below will do this 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] 
GO 
EXEC sp_addrolemember N'db_owner', N'DBUser1' 
GO

Now lets check from SQL Server Management Studio for the users we just created. Notice that the "AliasUser1" we aliased to "dbo" is not seen under users. SQL Server does not show aliased users in the "Users" list.

Figure 1 - Database Users in SQL Server Management Studio

So how do we find our aliased users in a database? Execute the query below:

select * from sys.sysusers

In the resultset below we see "AliasUser1" has a "\" in front of it, if we check the column "isaliased", 3rd column from right, it has a value of 1

Figure 2 - sys.sysusers

The command below can also be run to list all the users and aliases. In the output below, you can see the alias user "AliasUser1" does not show up as a user, but in the second resultset it shows up as an aliased user.

exec sp_helpuser

Figure 3 - sp_helpuser

Another interesting point to note is that, you cannot drop an aliased user using the sp_dropuser stored procedure, instead, use sp_dropalias.

By having a login use an alias to "dbo" this gives this account full access to your database, just like a db_owner. The downside is there is nowhere in SQL Server Management Studio that you can see this. You can try this for yourself. Follow the above steps in one of your databases and then login using login "AliasUser1" and password "test" and you see that this login can take any action it wants in the database where you created the alias.

Next Steps

Run sp_helpuser against your databases to see if you have aliases setup.
If there are aliased users that have "dbo" level rights look at removing these and using roles instead as stated below.
This alias feature has been discouraged by Microsoft for a long time, but it still made it to 2005, I think because of backward compatibility. This feature is removed in SQL 2008, see this important note below from SQL 2005 BOL:
- sp_addalias provides compatibility with earlier versions of SQL Server. This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. As an alternative to using aliases, the SQL Server 2005 Database Engine provides roles and the ability to grant permissions to roles. For more information, see CREATE USER (Transact-SQL) and CREATE ROLE (Transact-SQL).
So, if your applications are using aliases, it will not work in SQL 2008 so look at using a different approach as mentioned above

About the author

Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips