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.
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.
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.
- 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: 2009-01-28
About the author
View all my tips