Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Security issues when using aliased users in SQL Server


By:   |   Read Comments (2)   |   Related Tips: More > 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.

Database Users in SQL Server Management Studio

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


Last Update:






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

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, January 20, 2011 - 1:21:11 PM - Rahul Back To Top

Nice article.

I wish to know much more in details regarding the user access .i.e authuntication.
e.g. we can restrict user for modifications on the data.

How this is handled.

 

I will be glad to see more information regarding this.

thank you


Monday, February 09, 2009 - 5:24:49 AM - K. Brian Kelley Back To Top

I would not call this a security "hole" as that implies the developers missed something. Aliases are a left-over from the 6.5 days (and maybe even before that) and were recommended against in SQL Server 7.0. The reason aliases exist in SQL Server 2005 is that SQL Server 2005 supports database compatibility back to SQL Server 6.0. SQL Server 2008 only supports SQL Server 2000 and above. Since it was deprecated in SQL Server 7.0, the ability to use aliases is completely gone in SQL Server 2008.

 


Learn more about SQL Server tools